Evitar que la fórmula cambie de celda al ordenar

01/05/2006 - 12:41 por pacomar | Informe spam

Buenos días grupo.

Continuando lo aportado por Héctor Miguel en un post anterior donde me
explicaba la forma de quitar acentos, les cuento:

Con el fin de ordenarlas alfabéticamente (por población) y para
facilitar la navegación en una web mediante enlaces anterior y
siguiente; estoy haciendo una hoja de poblaciones y provincias y sus
correspondientes enlaces a la población anterior y siguiente que deben
aparecer de forma automática. En la Fila 1 están los rótulos de las
columnas y las columnas son: A:Población B: Provincia C:Enlace
pob_anterior D:Enlace pob_siguiente.
Los enlaces tienen esta estructura: "../alicante/benidorm.htm" , menos
el primero y el último donde cambian a: "alicante.htm" que es otra
página donde están listadas todas las poblaciones agrupadas por
provincias. Por lo tanto, si C3 hace relación a A2 con la fórmula
comentada anteriormente para quitar los acentos y sus correspondientes
& para completar, C2 tiene un valor fijo: "alicante.htm", ya que es la
primera y ésta celda siempre apuntará a "alicante.htm"
independientemente de la población que haya en cada momento en A2.
Esto mismo pasa en la última fila con datos en la hoja.

El problema que tengo es que cuando incluyo una población que se
coloca la primera o la última, al ordenar la columna poblaciones, las
fórmulas para los primeras o últimas celdas (las de después de
ordenar) de las columnas C y D devuelven un #!valor! al no encontrar lo
que se espera y las que estaban (antes de ordenar) primera o última
con un valor fijo: "alicante.htm", tampoco se corresponde con la nueva
ordenación.

Por otra parte, ¿se puede forzar a la hoja para que presente siempre
los datos ordenados por una columna concreta sin necesidad de picar en
el botón, o en su defecto se puede programar un botón para ordenar
por un rango de una columna?

Espero haberme explicado.

Saludos..

Paco.

PD. Héctor, perdona si no es ésta la forma, pero me ha parecido
interesante un post nuevo en vez de continuar en el anterior.

Siga el debate

12 respuestas

Tengo una respuesta

Preguntas similare

Leer las respuestas

#1 Héctor Miguel

02/05/2006 - 05:31 | Informe spam

hola, Paco !

1) 'ordenar' listados 'equivale a' [literalmente] RE-ESCRIBIR los datos... 'cambiando su posicion en el listado' <= OJO
[por lo cual]... el 'detalle real' se debe a que el listado [que ordenas] incluye formulas :-((
las cuales -aparentemente- 'permanecen SIN cambios' [a menos que esten 'enlazadas' con las celdas que 'cambian']

2) para 'auto-ordenar' listados, puedes usar codigo en el evento '_change' de la hoja con el listado...
de forma que se 'dispare' cuando algun 'cambio' ocurre EN la columna que 'comanda' en el orden del listado ;)

3) [creo que] para poder 'conjuntar' una -alternativa de posible- 'solucion' para AMBOS 'detalles'...
va a ser necesario que comentes cual debe ser la 'conexion' [o de donde se puede obtener/construir/...]
entre 'la poblacion/provincia'... y su 'anterior/siguiente' -?-
-> no se te olvide comentar 'cual' es [+/- exactamente]... la formula que estas usando :D

saludos,
hector.

__ consulta original __

Con el fin de ordenarlas... (por poblacion) y para facilitar la navegacion en una web
mediante enlaces anterior y siguiente; estoy haciendo una hoja de poblaciones y provincias
y sus correspondientes enlaces a la poblacion anterior y siguiente que deben aparecer de forma automatica.
En la Fila 1 estan los rotulos... : > A:Poblacion B: Provincia C:Enlace pob_anterior D:Enlace pob_siguiente.
Los enlaces tienen esta estructura: "../alicante/benidorm.htm" , menos el primero y el ultimo
donde cambian a: "alicante.htm" que es otra pagina donde estan listadas todas las poblaciones agrupadas por provincias.
... si C3 hace relacion a A2 con la formula... para quitar los acentos... C2 tiene un valor fijo: "alicante.htm"...
... Esto mismo pasa en la ultima fila con datos en la hoja.
... problema... cuando incluyo una poblacion que se coloca la primera o la ultima, al ordenar la columna poblaciones
las formulas... de las columnas C y D devuelven un #!valor! al no encontrar lo que se espera
y las que estaban (antes de ordenar)... con un valor fijo: "alicante.htm", tampoco se corresponde con la nueva ordenacion.
Por otra parte, se puede forzar a la hoja para que presente siempre los datos ordenados por una columna concreta
sin necesidad de picar en el boton, o en su defecto se puede programar un boton para ordenar por un rango de una columna?

Responder a este mensaje

#2 pacomar

02/05/2006 - 16:40 | Informe spam

Responder a este mensaje

#3 Héctor Miguel

02/05/2006 - 22:52 | Informe spam

hola, Paco !

1) se requiere 'anidar' 11funciones:
6 con sustituir [5 para quitar los acentos + 1 para 'cambiar' un espacio por una barra]
+ minusc + espacios + si-condicional + fila + esblanco
-> lo cual NO es posible :-(( a menos que la 'distribuyas' en dos [o mas] celdas... o -> nombres definidos :))

2) tomando la opcion de definir nombres... sigue estos pasos:
a) define 2 nombres [p.e. Enlace_ant y Enlace_sig]
b) usa las siguientes formulas:
=> IMPORTANTE: selecciona primero alguna celda en la fila 2 <= OJO
Enlace_ant -> =minusc(espacios($b2&si(fila()>2;" "&desref($a2;-1;);"")))
Enlace_sig -> =minusc(espacios($b2&si(esblanco(desref($a2;1;));"";" "&desref($a2;1;))))
c) selecciona la celda 'C2' [el primer 'enlace anterior'] e introduce la siguiente formula:
="../"&sustituir(sustituir(sustituir(sustituir(sustituir(sustituir(enlace_ant;"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u");" ";"/")&".htm"
d) selecciona la celda 'D2' [el primer 'enlace siguiente'] e introduce la siuguiente formula:
="../"&sustituir(sustituir(sustituir(sustituir(sustituir(sustituir(enlace_sig;"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u");" ";"/")&".htm"
=> es 'la misma' :)) solo se cambia la referencia al nombre: Enlace_ant por -> Enlace_sig :D
e) copia las [formula en las] celdas 'C2' y 'D2' -> al resto de las filas en las columnas 'C:D'

3) [ahora] los 'cambios' por alguna re/ordenacion de tu listado de poblaciones NO haran 'mella' en la 'secuencia' de las formulas ;)
-> solo toma en cuenta que 'mis supuestos' son:
a) la primer fila con datos ES la fila 2 <= en la definicion del nombre 'Enlace_ant' => Fila()>2
b) a la 'ultima' fila con datos... le sigue una celda VACIA ;) <= el 'esblanco(desref' en la definicion del nombre 'Enlace_sig'

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

___ consulta original __
1_|______A_______|______B______|_____________C_____________|____________D____________|
2_|POBLACION_____|PROVINCIA____|ENLACE_ANT.________________|ENLACE_SIG.______________|
3_|Alcoy_________|Alicante_____|alicante.htm_______________|../alicante/torrevieja.htm
4_|Torrevieja____|Alicante_____|../alicante/alcoy.htm______|../alicante/orihuela.htm
5_|Orihuela______|Alicante_____|../alicante/torrevieja.htm_|alicante.htm

La formula que se utiliza en C4 y D3,D4 es:
="../"&MINUSC(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(MINUSC(Dx)

;"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u"))
&"/"&MINUSC(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(IZQUIERDA(Cx;HALLAR("";Cx)-1)
;"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U"))&".htm"

Los valores de C3, C5 y D5 son valores fijos "alicante.htm"
... cuando se reordena alfabeticamente por la columna poblacion, C5 pasa a ser C4 con el consiguiente error en las formulas.

Responder a este mensaje

#4 pacomar

03/05/2006 - 10:22 | Informe spam

Buenos día Héctor.

Como siempre, gracias por tu tiempo y conocimientos.

Casi aciertas a la primera...

Ejemplo. Esto es lo que queremos ver

_|______A_______|______B______|_____________C_____________|____________D____________|
1_|POBLACION____|PROVINCIA____|ENLACE_ANT.________________|ENLACE_SIG.______________|
2_|Alcoy________|Alicante_____|andalucia.htm______________|../murcia/torrevieja.htm
3_|Torrevieja___|Murcia_______|../alicante/alcoy.htm______|../valencia/orihuela.htm
4_|Orihuela_cl__|Valencia_____|../murcia/torrevieja.htm___|andalucia.htm

Tus supuestos son correctos:
a) la primer fila con datos ES la fila 2
b) a la 'ultima' fila con datos... le sigue una celda VACIA

Ejemplo anterior con las fórmulas aplicadas:

_|______A_______|______B______|_____________C_____________|____________D____________|
1_|POBLACION____|PROVINCIA____|ENLACE_ANT.________________|ENLACE_SIG.______________|
2_|Alcoy________|Alicante_____|../provincia.htm___________|../alicante/torrevieja.htm
3_|Torrevieja___|Murcia_______|../alicante/poblacion.htm__|../murcia/orihuela/cl.htm
4_|Orihuela_cl__|Valencia_____|../murcia/alcoy.htm________|../valencia.htm

En el ejemplo, en C2 y D4 debe poner: "andalucia.htm" que son primero y
último del listado (esto es una constante que debe respetarse siempre:
en C2 y Cx=andalucia.htm), pero no definimos esto en ningún
¿"nombre"?, así que ahora en C2 aparece el rótulo de la columna que
hay en B2(PROVINCIA)"../provincia.htm" y en D4 "../valencia.htm"

Cuando hay un nombre de población compuesto como por ejemplo A4
(Orihuela cl), debe quedarse sólo con la parte de la izquierda. Ahora
lo que se ve en D3 es : "orihuela/cl/alicante.htm"

La estructura de los resultados en C y D debe ser: "
../provincia/población.htm" y tal como me lo has enviado, aunque con
valores de celdas incorrectas, aparece: "../población/provincia.htm"

A ver si con todo esto me explico y puedo transmitirte las ideas
adecuadamente.

Gracias.

Responder a este mensaje

#5 Héctor Miguel

03/05/2006 - 20:55 | Informe spam

hola, Paco !

prueba cambiando [solo] las formulas con las que se definen los nombres 'sugeridos' +/- a lo siguiente:
Enlace_ant:
=minusc(espacios(si(fila()>2;!$b1&" "&si(esnumero(hallar(" ";!$a1));izquierda(!$a1;hallar(" ";!$a1)-1);!$a1);"andalucia")))
Enlase_sig:
=minusc(espacios(si(esblanco(!$a3);"andalucia";!$b3&" "&si(esnumero(hallar(" ";!$a3));izquierda(!$a3;hallar(" ";!$a3)-1);!$a3))))

es probable que las formulas 'derivadas' de los nombres NO sean 'auto-actualizabes' [si no existen recalculos en sus celdas] :-(
a menos que les agregues [en ambas] una funcion que las vuelva 'volatiles' [p.e.]
a la parte final de la funcion izquierda(hallar(...)-1) -> cambiala por: -> izquierda(hallar(...)-1+0*hoy())

corre algunas pruebas y... si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.

Casi aciertas a la primera... Ejemplo. Esto es lo que queremos ver

a) la primer fila con datos ES la fila 2
b) a la 'ultima' fila con datos... le sigue una celda VACIA [...]
... en C2 y D4 debe poner: "andalucia.htm" que son primero y ultimo del listado
(esto es una constante que debe respetarse siempre: en C2 y Cx=andalucia.htm)
pero no definimos esto en ningun "nombre"?, asi que ahora en C2 aparece el rotulo
de la columna que hay en B2(PROVINCIA)"../provincia.htm" y en D4 "../valencia.htm"
Cuando hay un nombre de población compuesto como por ejemplo A4 (Orihuela cl)
debe quedarse sólo con la parte de la izquierda. Ahora lo que se ve en D3 es : "orihuela/cl/alicante.htm" ...

Responder a este mensaje

Ads by Google

Hacer una preguntaSiguiente

Tengo una respuesta

12 3

Busqueda sugerida