Validación de datos por lista y actualización automática ¿imposi bl

[Datos del entorno]
Windows Vista
Office 2007 RTM
Consulta sobre EXCEL 2007

[Lo que se intenta conseguir]
- Tenemos en *excel*hoja1* una *tabla1* (en versiones anteriores a la tabla
se le llamaba lista) de datos con tres columnas cuyos encabezados son:
REFERENCIA
TITULO DEL DOCUMENTO
CATEGORIA

- Con esa sencilla tabla (para empezar) se intenta tener cierto control
sobre los documentos de la empresa. Lo importante es la columna CATEGORIA.
Cada celda de la la columna CATEGORÍA, mediante la validación de datos por
lista, abre una lista situada en la misma hoja en el rango (z1:z100) y
permite seleccionar un valor.
Estos valores son las *categorías de los documentos* que yo llamo *CATDOC*,
por ejemplo: facturas, listado de trabajadores, etc, etc.

[El problema]
Si pasados unos dias queremos reorganizar el *CATDOC* (los datos del rango
z1:z100)... tenemos el problema de que la columna CATEGORÍAS de la *tabla1*
no se actualiza. Es decir, si en el *CATDOC* tenemos un dato "facturas" y
mañana lo queremos cambiar por "facturas 2007", en la tabla1 sigue
apareciendo "facturas"

[Lo he intentando solucionar... pero no lo consigo pulir]
Lo que he intentado hacer yo es lo siguiente:
- he transformado el rango (z1:z100) en otra tabla (tabla2) con dos
columnas. La primera columna contiene la referencia de categoria (1, 2, 3...)
y la columna 2 la descripción de la categoría. De forma que un ejemplo de
dato (fila) es: "1" / "facturas 2007"
- en la tabla1 tenemos:
*****una columna llamada *SELECTOR* que está validada con formato de lista y
consulta a la columna 2 de la tabla 2. Es decir, nos permite seleccionar la
categoría.
*****una columna llamada *IDENTIFICADOR* que automáticamente *busca* en la
tabla 2 / columna 1 la referencia de la categoría (1, 2, 3, ...) y la
muestra. La búsqueda la hace condicionando el valor de la columna
IDENTIFICADOR.
*****una columna llamada RESULTADO que *busca* en la tabla 2 columna 2 la
descripcion de categoría y la muestra. La búsqueda la hace condicionando el
valor de la columna *identificador*

He conseguido que funcione! Ahora cuando cambio una descripcion de categoría
en la tabla 2, este cambio se muestra en la tabla 1 de forma automática.

EL PROBLEMA es que la instrucción BUSCAR da resultados ERRONEOS cuando las
columnas de la tabla 2 no están ordenadas (de mayor a menor por ejemplo, esto
está comprobado y lo pone en la ayuda de excel).
Vale, dirías que la solución es ordenar las columnas y realmente esa es la
solución.
PERO NO!, porque se emplea BUSCAR en dos columnas distintas, por lo que
cuando una columna está ordenada la otra no lo está y viceversa.

Por lo tanto es imposible utilizar BUSCAR de esta forma.

Necesitaría un equivalente a la opción buscar que sea independiente del
orden del lugar donde está buscando U otro método para hacer esto. En ACCESS
a lo que quiero hacer se le llama cuadro combinado.

Me gustaría hacerlo sin macros ni código, porque cuando le pasas la hoja a
alguien que no tiene ni idea de excel suele haber problemas (que si mensajes
advirtiendo macros, que si validación de código, etc).

Perdon por el rollo!
A ver si me he explicado con claridad o mejor me dedico a politico (que así
no importa lo que diga, con que no se me entienda tengo el puesto de
presidente asegurado) :)

Hola, Maximo.

Totalmente de acuerdo con tus comentarios. La función Buscar no aplica para
tu problema, sugiero reemplazarla con la función Coincidir( ) y con
Indirecto( ).

A mi criterio, también estás muy bien encaminado al agregar las columnas
SELECTOR e IDENTIFICADOR. Muchas personas se resisten a agregar columnas, y
la solución se complica enormemente.

¿Cómo hacerlo? Crea un libro en blanco, aplica unos minutos en lo
siguiente, y me cuentas...

Primero prepara tu libro electrónico:

Escribe lo siguiente:
A1: Referencia
B1: Documento
C1: Categoría
E1: Selector
F1: ID

En la columna Z, escribe algunas de tus categorías.
En la columna AA, escribe los ID para cada categoría que ingresaste en la
columna Z.

En la celda AC1, copia y pega esta fórmula:
=IF(ISBLANK(AB1),"",INDIRECT(("aa"&MATCH(AB1,INDIRECT($AC$2),0))))
o en español:
=SI(ESBLANCO(AB1),"",INDIRECTo(("aa"&COINCIDIR(AB1,INDIRECTO($AC$2),0))))
(disculpa si tengo errores de sintaxis, pero me he instalado la versión en
inglés, y no tengo a mano la versión en español.

En la celda AC2, copya y pega esta fórmula:
="Z1:Z"&COUNTA(Z:Z)
o en español:
="Z1:Z"&CONTARA(Z:Z)

En la celda AC3, copia y pega esta fórmula:
="AA1:AA"&COUNTA(Z:Z)
o en español:
="AA1:AA"&CONTARA(Z:Z)

Selecciona varias celdas de la columna E, a partir de E2, y aplícales una
validación por lista. En el cuadro "origen de la lista", pega la siguiente
fórmula:
=INDIRECT($AC$2)
o en español:
=INDIRECTO($AC$2)

En la celda C2, copia y pega la siguiente fórmula:
=IF(ISBLANK(E2),"",INDIRECT("Z"&MATCH(F2,INDIRECT($AC$3),0)))
o en español:
=SI(ESBLANCO(E2),"",INDIRECTO("Z"&COINCIDIR(F2,INDIRECTO($AC$3),0)))
y la copias hasta la celda E11.


Ahora, lo que vas a tener que estar haciendo constantemente:
Copia la fórmula de AC1, y pégala en el rango F2:F11.
Conforme vayas ingresando nuevos documentos, deberás copiar AC1 a las filas
respectivas de los documentos nuevos en la columna F. Esta fórmula te
localiza el ID de la categoría que has escrito en la columna Selector.

Ahora supongamos que vas a asignar la primera categoría.
1. En la celda E2, selecciona de la lista la categoría que quieras.
2. Muévete a la celda F2, y pulsa la tecla F2 y luego F9. Esto hará que el
ID que se ha encontrado por fórmula se fije como valor y ya no varíe.
Repite los pasos anteriores con varios selectores.

Y la prueba de fuego:
Cambia el nombre de algunas categorías en la columna Z.
Ordena la columna Z.
Ordena la columna AA.

Si todo funciona bien, estudia las fórmulas, y me debes la
vicepresidencia!... ;-)




"Maximo Boada" escribió:

> [Datos del entorno]
> Windows Vista
> Office 2007 RTM
> Consulta sobre EXCEL 2007
>
> [Lo que se intenta conseguir]
> - Tenemos en *excel*hoja1* una *tabla1* (en versiones anteriores a la tabla
> se le llamaba lista) de datos con tres columnas cuyos encabezados son:
> REFERENCIA
> TITULO DEL DOCUMENTO
> CATEGORIA
>
> - Con esa sencilla tabla (para empezar) se intenta tener cierto control
> sobre los documentos de la empresa. Lo importante es la columna CATEGORIA.
> Cada celda de la la columna CATEGORÍA, mediante la validación de datos por
> lista, abre una lista situada en la misma hoja en el rango (z1:z100) y
> permite seleccionar un valor.
> Estos valores son las *categorías de los documentos* que yo llamo *CATDOC*,
> por ejemplo: facturas, listado de trabajadores, etc, etc.
>
> [El problema]
> Si pasados unos dias queremos reorganizar el *CATDOC* (los datos del rango
> z1:z100)... tenemos el problema de que la columna CATEGORÍAS de la *tabla1*
> no se actualiza. Es decir, si en el *CATDOC* tenemos un dato "facturas" y
> mañana lo queremos cambiar por "facturas 2007", en la tabla1 sigue
> apareciendo "facturas"
>
> [Lo he intentando solucionar... pero no lo consigo pulir]
> Lo que he intentado hacer yo es lo siguiente:
> - he transformado el rango (z1:z100) en otra tabla (tabla2) con dos
> columnas. La primera columna contiene la referencia de categoria (1, 2, 3...)
> y la columna 2 la descripción de la categoría. De forma que un ejemplo de
> dato (fila) es: "1" / "facturas 2007"
> - en la tabla1 tenemos:
> *****una columna llamada *SELECTOR* que está validada con formato de lista y
> consulta a la columna 2 de la tabla 2. Es decir, nos permite seleccionar la
> categoría.
> *****una columna llamada *IDENTIFICADOR* que automáticamente *busca* en la
> tabla 2 / columna 1 la referencia de la categoría (1, 2, 3, ...) y la
> muestra. La búsqueda la hace condicionando el valor de la columna
> IDENTIFICADOR.
> *****una columna llamada RESULTADO que *busca* en la tabla 2 columna 2 la
> descripcion de categoría y la muestra. La búsqueda la hace condicionando el
> valor de la columna *identificador*
>
> He conseguido que funcione! Ahora cuando cambio una descripcion de categoría
> en la tabla 2, este cambio se muestra en la tabla 1 de forma automática.
>
> EL PROBLEMA es que la instrucción BUSCAR da resultados ERRONEOS cuando las
> columnas de la tabla 2 no están ordenadas (de mayor a menor por ejemplo, esto
> está comprobado y lo pone en la ayuda de excel).
> Vale, dirías que la solución es ordenar las columnas y realmente esa es la
> solución.
> PERO NO!, porque se emplea BUSCAR en dos columnas distintas, por lo que
> cuando una columna está ordenada la otra no lo está y viceversa.
>
> Por lo tanto es imposible utilizar BUSCAR de esta forma.
>
> Necesitaría un equivalente a la opción buscar que sea independiente del
> orden del lugar donde está buscando U otro método para hacer esto. En ACCESS
> a lo que quiero hacer se le llama cuadro combinado.
>
> Me gustaría hacerlo sin macros ni código, porque cuando le pasas la hoja a
> alguien que no tiene ni idea de excel suele haber problemas (que si mensajes
> advirtiendo macros, que si validación de código, etc).
>
> Perdon por el rollo!
> A ver si me he explicado con claridad o mejor me dedico a politico (que así
> no importa lo que diga, con que no se me entienda tengo el puesto de
> presidente asegurado) :)
>
>
>
>
>

Ante todo, David:
1.- Gracias por la respuesta.
2.- Gracias por "traducir" las fórmulas al español
3.- Y adelantarte que HA FUNCIONADO! :)

Cuando vi tu respuesta lo primero que hice fue seguir tus instrucciones sin
empaparme las fórmulas utilizadas (ni sus funciones). Pasó lo que tenía que
pasar: ERROR!!

Después, con más tiempo intenté entender las formulas y cómo utilizabas las
funciones (funciones como INDIRECTO de las que ni siquiera sabia que
existian)... nada, era un rompecabezas. Así que ante el fracaso de "el camino
corto" me puse a imprimir la ayuda de las funciones y a practicar con ellas.

Como siempre pasa en informática, el camino más largo es con el que aprendes
:)

Paso a señalar los cambios que hice a las fórmulas por si a alguien mas le
interesa el tema. Utilizando directamente tus fórmulas me daba errores,
después de empaparme las funciones hice un cambio (sustituir alguna coma *,*
por punto y coma *;*). Etiqueto como [ORIGINAL] tus fórmulas y como
[MODIDIFICADO] tus fórmulas con mi humilde cambio:

[ORIGINAL]
=SI(ESBLANCO(AB1),"",INDIRECTo(("aa"&COINCIDIR(AB1,INDIRECTO($AC$2),0))))
[MODIFICADO]
=SI(ESBLANCO(AB1);"";INDIRECTO(("aa"&COINCIDIR(AB1;INDIRECTO($AC$2);0))))

[ORIGINAL]
=SI(ESBLANCO(E2),"",INDIRECTO("Z"&COINCIDIR(F2,INDIRECTO($AC$3),0)))
[MODIFICADO]
=SI(ESBLANCO(E2);"";INDIRECTO("Z"&COINCIDIR(F2;INDIRECTO($AC$3);0)))

El resultado ha sido muy bueno:
- se puede cambiar las categorías de orden
- se puede modificar una categoría y este cambio es reflejado en la tabla
principal
- se pueden añadir categorías
- en definitiva, un excelente trabajo.

Me ha sorprendido especialmente el uso que haces de INDIRECTO. Cuando me lei
la ayuda de esta función, sinceramente, la vi MUY "liante". Es una de esas
funciones que si la utilizas debes comentarla muy bien, porque si otro dia
decides modificar la hoja seguro que te pierdes (mi memoria no es muy buena).

En el fondo me queda una sensación de... ¿decepción? Sí, creo que si.
Decepción respecto a Excel. Un problema aparentemente sencillo (un cuadro
combinado) acaba en una hoja que para un principiante es casi un puzle
imposible.

Excel ha avanzado bastante en el tema de los formatos condicionales (no veas
la de código que ahorra), pero con la validación de datos no se ha esforzado
demasiado. Sería MUY UTIL tener cuadros combinados "formateables" sin la
necesidad de código y a los cuales se les pudiese asociar acciones a sus
eventos sin la necesidad de código. Algo así como: "al cambiar el dato de la
pestaña desplegable" --> "cambiar el valor de la celda x por el de la celda
xx" o "al cerrar la pestaña desplegable" --> "aplicar tal o cual fórmula"...
todo en plan visual :)

De nuevo muchas gracias por todo y quedas nominado para la cebeza de partido
;)

Un saludo!





"David" wrote:

> Hola, Maximo.
>
> Totalmente de acuerdo con tus comentarios. La función Buscar no aplica para
> tu problema, sugiero reemplazarla con la función Coincidir( ) y con
> Indirecto( ).
>
> A mi criterio, también estás muy bien encaminado al agregar las columnas
> SELECTOR e IDENTIFICADOR. Muchas personas se resisten a agregar columnas, y
> la solución se complica enormemente.
>
> ¿Cómo hacerlo? Crea un libro en blanco, aplica unos minutos en lo
> siguiente, y me cuentas...
>
> Primero prepara tu libro electrónico:
>
> Escribe lo siguiente:
> A1: Referencia
> B1: Documento
> C1: Categoría
> E1: Selector
> F1: ID
>
> En la columna Z, escribe algunas de tus categorías.
> En la columna AA, escribe los ID para cada categoría que ingresaste en la
> columna Z.
>
> En la celda AC1, copia y pega esta fórmula:
> =IF(ISBLANK(AB1),"",INDIRECT(("aa"&MATCH(AB1,INDIRECT($AC$2),0))))
> o en español:
> =SI(ESBLANCO(AB1),"",INDIRECTo(("aa"&COINCIDIR(AB1,INDIRECTO($AC$2),0))))
> (disculpa si tengo errores de sintaxis, pero me he instalado la versión en
> inglés, y no tengo a mano la versión en español.
>
> En la celda AC2, copya y pega esta fórmula:
> ="Z1:Z"&COUNTA(Z:Z)
> o en español:
> ="Z1:Z"&CONTARA(Z:Z)
>
> En la celda AC3, copia y pega esta fórmula:
> ="AA1:AA"&COUNTA(Z:Z)
> o en español:
> ="AA1:AA"&CONTARA(Z:Z)
>
> Selecciona varias celdas de la columna E, a partir de E2, y aplícales una
> validación por lista. En el cuadro "origen de la lista", pega la siguiente
> fórmula:
> =INDIRECT($AC$2)
> o en español:
> =INDIRECTO($AC$2)
>
> En la celda C2, copia y pega la siguiente fórmula:
> =IF(ISBLANK(E2),"",INDIRECT("Z"&MATCH(F2,INDIRECT($AC$3),0)))
> o en español:
> =SI(ESBLANCO(E2),"",INDIRECTO("Z"&COINCIDIR(F2,INDIRECTO($AC$3),0)))
> y la copias hasta la celda E11.
>
>
> Ahora, lo que vas a tener que estar haciendo constantemente:
> Copia la fórmula de AC1, y pégala en el rango F2:F11.
> Conforme vayas ingresando nuevos documentos, deberás copiar AC1 a las filas
> respectivas de los documentos nuevos en la columna F. Esta fórmula te
> localiza el ID de la categoría que has escrito en la columna Selector.
>
> Ahora supongamos que vas a asignar la primera categoría.
> 1. En la celda E2, selecciona de la lista la categoría que quieras.
> 2. Muévete a la celda F2, y pulsa la tecla F2 y luego F9. Esto hará que el
> ID que se ha encontrado por fórmula se fije como valor y ya no varíe.
> Repite los pasos anteriores con varios selectores.
>
> Y la prueba de fuego:
> Cambia el nombre de algunas categorías en la columna Z.
> Ordena la columna Z.
> Ordena la columna AA.
>
> Si todo funciona bien, estudia las fórmulas, y me debes la
> vicepresidencia!... ;-)
>
>
>
>
> "Maximo Boada" escribió:
>
> > [Datos del entorno]
> > Windows Vista
> > Office 2007 RTM
> > Consulta sobre EXCEL 2007
> >
> > [Lo que se intenta conseguir]
> > - Tenemos en *excel*hoja1* una *tabla1* (en versiones anteriores a la tabla
> > se le llamaba lista) de datos con tres columnas cuyos encabezados son:
> > REFERENCIA
> > TITULO DEL DOCUMENTO
> > CATEGORIA
> >
> > - Con esa sencilla tabla (para empezar) se intenta tener cierto control
> > sobre los documentos de la empresa. Lo importante es la columna CATEGORIA.
> > Cada celda de la la columna CATEGORÍA, mediante la validación de datos por
> > lista, abre una lista situada en la misma hoja en el rango (z1:z100) y
> > permite seleccionar un valor.
> > Estos valores son las *categorías de los documentos* que yo llamo *CATDOC*,
> > por ejemplo: facturas, listado de trabajadores, etc, etc.
> >
> > [El problema]
> > Si pasados unos dias queremos reorganizar el *CATDOC* (los datos del rango
> > z1:z100)... tenemos el problema de que la columna CATEGORÍAS de la *tabla1*
> > no se actualiza. Es decir, si en el *CATDOC* tenemos un dato "facturas" y
> > mañana lo queremos cambiar por "facturas 2007", en la tabla1 sigue
> > apareciendo "facturas"
> >
> > [Lo he intentando solucionar... pero no lo consigo pulir]
> > Lo que he intentado hacer yo es lo siguiente:
> > - he transformado el rango (z1:z100) en otra tabla (tabla2) con dos
> > columnas. La primera columna contiene la referencia de categoria (1, 2, 3...)
> > y la columna 2 la descripción de la categoría. De forma que un ejemplo de
> > dato (fila) es: "1" / "facturas 2007"
> > - en la tabla1 tenemos:
> > *****una columna llamada *SELECTOR* que está validada con formato de lista y
> > consulta a la columna 2 de la tabla 2. Es decir, nos permite seleccionar la
> > categoría.
> > *****una columna llamada *IDENTIFICADOR* que automáticamente *busca* en la
> > tabla 2 / columna 1 la referencia de la categoría (1, 2, 3, ...) y la
> > muestra. La búsqueda la hace condicionando el valor de la columna
> > IDENTIFICADOR.
> > *****una columna llamada RESULTADO que *busca* en la tabla 2 columna 2 la
> > descripcion de categoría y la muestra. La búsqueda la hace condicionando el
> > valor de la columna *identificador*
> >
> > He conseguido que funcione! Ahora cuando cambio una descripcion de categoría
> > en la tabla 2, este cambio se muestra en la tabla 1 de forma automática.
> >
> > EL PROBLEMA es que la instrucción BUSCAR da resultados ERRONEOS cuando las
> > columnas de la tabla 2 no están ordenadas (de mayor a menor por ejemplo, esto
> > está comprobado y lo pone en la ayuda de excel).
> > Vale, dirías que la solución es ordenar las columnas y realmente esa es la
> > solución.
> > PERO NO!, porque se emplea BUSCAR en dos columnas distintas, por lo que
> > cuando una columna está ordenada la otra no lo está y viceversa.
> >
> > Por lo tanto es imposible utilizar BUSCAR de esta forma.
> >
> > Necesitaría un equivalente a la opción buscar que sea independiente del
> > orden del lugar donde está buscando U otro método para hacer esto. En ACCESS
> > a lo que quiero hacer se le llama cuadro combinado.
> >
> > Me gustaría hacerlo sin macros ni código, porque cuando le pasas la hoja a
> > alguien que no tiene ni idea de excel suele haber problemas (que si mensajes
> > advirtiendo macros, que si validación de código, etc).
> >
> > Perdon por el rollo!
> > A ver si me he explicado con claridad o mejor me dedico a politico (que así
> > no importa lo que diga, con que no se me entienda tengo el puesto de
> > presidente asegurado) :)
> >
> >
> >
> >
> >