BUSCARV Por si no te puedo mandar un adjunto
Tengo un libro con tres hojas (hoja1, hoja2 y hoja3). Según te indico:
Hoja1
A1: NUM-FICHA; B1: AÑO FICHA; C1: NOMBRE; D1: APELLIDO1; E1: APELLIDO2; F1: DNI
A2: 1200; B2: 01; C2: JUAN; D2: GARCIA; E2: GOMEZ; F2: 45.262.262
A3: 1200; B3: 02; C3: JOSE; D3: GOMEZ; E3: LOPEZ; F3: 25.283.283
A4: 521; B4: 99; C4: PEDRO; D4: LOPEZ; E4: GARCIA; F4: 81.456.456
A5: 634; B5: 00; C5: JORGE; D5: SUAREZ; E5: FEIJOO; F5: 13.485.485
A6: 521; B6: 01; C6: JUAN; D6: LUCAS; E6: PEREZ; F6: 22.875.875
HOJA2
A1: COD-ACCION; B1: ACCION
A2: A; B2: LLAMAR
A3: B; B3: ESCRIBIR
A4: C; B4: CONTACTAR
A5: 10; B5: PROVEEDOR
A6: 11; B6: CLIENTE
A7: 12; B7: DIRECTIVO
A8: 100; B8: COMIDA DE EMPRESA
A9: 101; B9: CENA DE DESPEDIDA
A10: 102; B10: COMIDA DE PRESENTACIÓN
HOJA3 (A modo de ejemplo)
A1: NUM-FICHA; B1: AÑO FICHA; C1: NOMBRE; D1: APELLIDO1; E1: APELLIDO2; F1: DNI; G1: COD-ACCION; H1: ACCION
A2: 521; B2: 99; C2: PEDRO; D2: LOPEZ; E2: GARCIA; F2: 81.456.456; G2: A; H2: LLAMAR
A3: 521; B3: 01; C3: JUAN; D3: LUCAS; E3: PEREZ; F3: 22.875.875; G3: 10; H3: PROVEEDOR
A4: 1200; B4: 01; C4: JUAN; D4: GARCIA; E4: GOMEZ; F4: 45.262.262; G4: A10; H4: LLAMAR PROVEEDOR
A5: 634; B5: 00; C5: JORGE; D5: SUAREZ; E5: FEIJOO; F5: 13.485.485; G5: 101; H5: ESCRIBIR DIRECTIVO
A6: 1200; B6: 02; C6: JOSE; D6: GOMEZ; E6: LOPEZ; F6: 25.283.283; G6: 102-C11; H6: COMIDA PRESENTACIÓN-CONTACTAR CLIENTE
A7: 925; B7: 01; C7: No existe
A8: 1200; B8: 99; C8: No existe
Me gustaría usar la hoja3 del libro como extracción de datos (a modo de base de datos) de las hojas 1 y 2. Las celdas A2 y B2 de esta hoja3 empezarían vacías y el resto de esa fila 2 contendrían las fórmulas adecuadas, las celdas A3 y B3 de esa misma hoja3 también irían vacías y el resto de esa fila 3 contendrían las fórmulas adecuadas, etc, etc, de manera que al introducir en la celda A2 el número de ficha y además el año correspondiente a esa ficha en la celda B2, en la celdas C2, D2, E2 y F2, te devuelva su nombre, apellido1 y apellido2, y DNI correspondiente, teniendo en cuenta que hay números de ficha iguales pero con diferentes años y correspondientes a diferentes personas. He probado con la función:
=SI(B2<>"";BUSCARV(A2;Hoja1!$A$2:$F$197;2;FALSO);"") adaptada a cada celda C2, D2, E2 y F2
pero no distingue entre años por lo que siempre me da los datos correspondientes al primer número de ficha que hay, aunque pongas otro año en B2. Te pongo la hoja3 rellena con los datos que deberían salir al teclear los distintos números de ficha con su correspondiente año. También si al comprobar que el numero de ficha y año tecleados en las celdas A2 y B2 (siempre combinados) no corresponde a ningún dato de la hoja1, que devuelva el mensaje "No existe", solo en la celda C2 y que las demás celdas se queden en blanco.
Lo que viene a continuación pienso que es lo más difícil y creo que habrá que meter código visual basic, pero tengo confianza en que lo resolverás. Me gustaría que en esa misma hoja3 la celda G2, estuviera vacía y la celda H2 contuviera la fórmula o código correspondiente, de manera que sea capaz de distinguir si solo tecleas un carácter (correspondiente a las letras (y solo letras) A, B, C ... Z), dos caracteres (correspondientes a los números (y solo números) 10, 11, 12 ... 99), o tres caracterers (100, 101, 102 ... 999 o combinación de las letras con los dos números A10, A11 ... A99, B10, B11 ... B99, C10 . Z99). Como está muy lioso te pongo el ejemplo de la hoja3 con las posibles combinaciones. Por ejemplo si solo tecleas una letra en G2, que te devuelva su acción correspondiente en H2, si solo tecleas dos cifras correspondientes del 10 al 99 en G2, que te devuelva su acción correspondiente en H2, pero si tecleas una letra, A por ejemplo con dos cifras, 10 por ejemplo, lo que haría un total de tres caracteres: A10, que te devuelva su acción correspondiente en H2 pero separada con un espacio o un punto, de la manera del ejemplo: LLAMAR PROVEEDOR o LLAMAR. PROVEEDOR y eso no sería todo, si tecleamos varios códigos de tres caracteres bien sean solo números del 100 al 999 o bien sean combinaciones de letras con dos números, separados con un guión y hasta un máximo de cinco códigos, que te devuelva las acciones correspondientes a los 2,3,4 o 5 códigos tecleados y separados por el mismo guión. En el ejemplo: G6: 102-C11; H6: COMIDA PRESENTACIÓN-CONTACTAR CLIENTE. Ya se que es muy difícil de conseguir ésta última parte, pero se trata de no tener que rellenar la hoja 2 con la cantidad enorme de combinaciones que existen de códigos, con el añadido de que si pones un nuevo código de acción habría que rellenar un montón de filas con las combinaciones correspondientes. Una idea que podría facilitar esto sería, si no es posible distinguir entre un carácter solo, o dos caracteres solos o tres, obligar a que en la celda G2 siempre haya códigos de tres caracteres (como una especie de máscara de entrada), bien sea una combinación de letras con números de dos cifras o números de tres cifras, o combinación de varios códigos de tres cifras separados con guión hasta un máximo de cinco (en este caso no sería obligatorio rellenar los cinco códigos), de manera que la combinación de las letras con el número 00, ejemplo A00 o B00, equivaldría solamente al código de acción de A o B, y de la misma forma la combinación del número 0 con las cifras del 10 al 99, ejemplo 010 o 021, equivaldría solamente al código de acción de 10 o 21. La idea sería que el 0 al principio o los dos 00 al final nos den vacío, sin espacios ni puntos ni nada, solo el código correspondiente a letra o dos cifras que si lo llevan.
Ejemplo (que no viene así en la hoja3): G2: A00; H2: LLAMAR o bien G2: 010; H2: Proveedor o bien G2: 100-011-B00-101-C12; H2: COMIDA EMPRESA-CLIENTE-ESCRIBIR-CENA DE DESPEDIDA-CONTACTAR DIRECTIVO. Repito esto es solo una idea por si no se puede realizar o es muy difícil, escribiendo solo un carácter o solo dos.
Como comprobarás los datos de las acciones no tienen mucho sentido pero son solo a modo de ejemplo, adaptándolo a lo que yo busco si tendría sentido.
Otra cosa más, es posible que el año cause problemas, puesto que si tu pones 01 sin la comilla ' delante (es decir '01), al salir de la celda se queda en 1 y si pones 00 se queda en 0, por tanto, ¿habría alguna manera de solucionar esto sin tener que teclear la comilla ' antes del 01 y que se quedara el 01 como año tecleado?
Perdona si te pongo cosas que para ti son obvias pero es que necesito ponerlas para que se entienda, porque como sé si te puede mandar el archivo a tu correo para que lo vieras mejor, pues te lo he puesto así.
Un saludo fejoal y espero tu respuesta con una solución satisfactoria. Si no fuera posible la segunda parte al menos la primera, aunque la que más me ayudaría es la segunda parte.
P.D.: Si pudieras mandarme la solución por mail a parte de contestarme por aquí, te estaría...
Hoja1
A1: NUM-FICHA; B1: AÑO FICHA; C1: NOMBRE; D1: APELLIDO1; E1: APELLIDO2; F1: DNI
A2: 1200; B2: 01; C2: JUAN; D2: GARCIA; E2: GOMEZ; F2: 45.262.262
A3: 1200; B3: 02; C3: JOSE; D3: GOMEZ; E3: LOPEZ; F3: 25.283.283
A4: 521; B4: 99; C4: PEDRO; D4: LOPEZ; E4: GARCIA; F4: 81.456.456
A5: 634; B5: 00; C5: JORGE; D5: SUAREZ; E5: FEIJOO; F5: 13.485.485
A6: 521; B6: 01; C6: JUAN; D6: LUCAS; E6: PEREZ; F6: 22.875.875
HOJA2
A1: COD-ACCION; B1: ACCION
A2: A; B2: LLAMAR
A3: B; B3: ESCRIBIR
A4: C; B4: CONTACTAR
A5: 10; B5: PROVEEDOR
A6: 11; B6: CLIENTE
A7: 12; B7: DIRECTIVO
A8: 100; B8: COMIDA DE EMPRESA
A9: 101; B9: CENA DE DESPEDIDA
A10: 102; B10: COMIDA DE PRESENTACIÓN
HOJA3 (A modo de ejemplo)
A1: NUM-FICHA; B1: AÑO FICHA; C1: NOMBRE; D1: APELLIDO1; E1: APELLIDO2; F1: DNI; G1: COD-ACCION; H1: ACCION
A2: 521; B2: 99; C2: PEDRO; D2: LOPEZ; E2: GARCIA; F2: 81.456.456; G2: A; H2: LLAMAR
A3: 521; B3: 01; C3: JUAN; D3: LUCAS; E3: PEREZ; F3: 22.875.875; G3: 10; H3: PROVEEDOR
A4: 1200; B4: 01; C4: JUAN; D4: GARCIA; E4: GOMEZ; F4: 45.262.262; G4: A10; H4: LLAMAR PROVEEDOR
A5: 634; B5: 00; C5: JORGE; D5: SUAREZ; E5: FEIJOO; F5: 13.485.485; G5: 101; H5: ESCRIBIR DIRECTIVO
A6: 1200; B6: 02; C6: JOSE; D6: GOMEZ; E6: LOPEZ; F6: 25.283.283; G6: 102-C11; H6: COMIDA PRESENTACIÓN-CONTACTAR CLIENTE
A7: 925; B7: 01; C7: No existe
A8: 1200; B8: 99; C8: No existe
Me gustaría usar la hoja3 del libro como extracción de datos (a modo de base de datos) de las hojas 1 y 2. Las celdas A2 y B2 de esta hoja3 empezarían vacías y el resto de esa fila 2 contendrían las fórmulas adecuadas, las celdas A3 y B3 de esa misma hoja3 también irían vacías y el resto de esa fila 3 contendrían las fórmulas adecuadas, etc, etc, de manera que al introducir en la celda A2 el número de ficha y además el año correspondiente a esa ficha en la celda B2, en la celdas C2, D2, E2 y F2, te devuelva su nombre, apellido1 y apellido2, y DNI correspondiente, teniendo en cuenta que hay números de ficha iguales pero con diferentes años y correspondientes a diferentes personas. He probado con la función:
=SI(B2<>"";BUSCARV(A2;Hoja1!$A$2:$F$197;2;FALSO);"") adaptada a cada celda C2, D2, E2 y F2
pero no distingue entre años por lo que siempre me da los datos correspondientes al primer número de ficha que hay, aunque pongas otro año en B2. Te pongo la hoja3 rellena con los datos que deberían salir al teclear los distintos números de ficha con su correspondiente año. También si al comprobar que el numero de ficha y año tecleados en las celdas A2 y B2 (siempre combinados) no corresponde a ningún dato de la hoja1, que devuelva el mensaje "No existe", solo en la celda C2 y que las demás celdas se queden en blanco.
Lo que viene a continuación pienso que es lo más difícil y creo que habrá que meter código visual basic, pero tengo confianza en que lo resolverás. Me gustaría que en esa misma hoja3 la celda G2, estuviera vacía y la celda H2 contuviera la fórmula o código correspondiente, de manera que sea capaz de distinguir si solo tecleas un carácter (correspondiente a las letras (y solo letras) A, B, C ... Z), dos caracteres (correspondientes a los números (y solo números) 10, 11, 12 ... 99), o tres caracterers (100, 101, 102 ... 999 o combinación de las letras con los dos números A10, A11 ... A99, B10, B11 ... B99, C10 . Z99). Como está muy lioso te pongo el ejemplo de la hoja3 con las posibles combinaciones. Por ejemplo si solo tecleas una letra en G2, que te devuelva su acción correspondiente en H2, si solo tecleas dos cifras correspondientes del 10 al 99 en G2, que te devuelva su acción correspondiente en H2, pero si tecleas una letra, A por ejemplo con dos cifras, 10 por ejemplo, lo que haría un total de tres caracteres: A10, que te devuelva su acción correspondiente en H2 pero separada con un espacio o un punto, de la manera del ejemplo: LLAMAR PROVEEDOR o LLAMAR. PROVEEDOR y eso no sería todo, si tecleamos varios códigos de tres caracteres bien sean solo números del 100 al 999 o bien sean combinaciones de letras con dos números, separados con un guión y hasta un máximo de cinco códigos, que te devuelva las acciones correspondientes a los 2,3,4 o 5 códigos tecleados y separados por el mismo guión. En el ejemplo: G6: 102-C11; H6: COMIDA PRESENTACIÓN-CONTACTAR CLIENTE. Ya se que es muy difícil de conseguir ésta última parte, pero se trata de no tener que rellenar la hoja 2 con la cantidad enorme de combinaciones que existen de códigos, con el añadido de que si pones un nuevo código de acción habría que rellenar un montón de filas con las combinaciones correspondientes. Una idea que podría facilitar esto sería, si no es posible distinguir entre un carácter solo, o dos caracteres solos o tres, obligar a que en la celda G2 siempre haya códigos de tres caracteres (como una especie de máscara de entrada), bien sea una combinación de letras con números de dos cifras o números de tres cifras, o combinación de varios códigos de tres cifras separados con guión hasta un máximo de cinco (en este caso no sería obligatorio rellenar los cinco códigos), de manera que la combinación de las letras con el número 00, ejemplo A00 o B00, equivaldría solamente al código de acción de A o B, y de la misma forma la combinación del número 0 con las cifras del 10 al 99, ejemplo 010 o 021, equivaldría solamente al código de acción de 10 o 21. La idea sería que el 0 al principio o los dos 00 al final nos den vacío, sin espacios ni puntos ni nada, solo el código correspondiente a letra o dos cifras que si lo llevan.
Ejemplo (que no viene así en la hoja3): G2: A00; H2: LLAMAR o bien G2: 010; H2: Proveedor o bien G2: 100-011-B00-101-C12; H2: COMIDA EMPRESA-CLIENTE-ESCRIBIR-CENA DE DESPEDIDA-CONTACTAR DIRECTIVO. Repito esto es solo una idea por si no se puede realizar o es muy difícil, escribiendo solo un carácter o solo dos.
Como comprobarás los datos de las acciones no tienen mucho sentido pero son solo a modo de ejemplo, adaptándolo a lo que yo busco si tendría sentido.
Otra cosa más, es posible que el año cause problemas, puesto que si tu pones 01 sin la comilla ' delante (es decir '01), al salir de la celda se queda en 1 y si pones 00 se queda en 0, por tanto, ¿habría alguna manera de solucionar esto sin tener que teclear la comilla ' antes del 01 y que se quedara el 01 como año tecleado?
Perdona si te pongo cosas que para ti son obvias pero es que necesito ponerlas para que se entienda, porque como sé si te puede mandar el archivo a tu correo para que lo vieras mejor, pues te lo he puesto así.
Un saludo fejoal y espero tu respuesta con una solución satisfactoria. Si no fuera posible la segunda parte al menos la primera, aunque la que más me ayudaría es la segunda parte.
P.D.: Si pudieras mandarme la solución por mail a parte de contestarme por aquí, te estaría...
1 Respuesta
Respuesta de fejoal
1
Como habrás notado al redactarlo, hay bastante por hacer aquí.
En principio, para evitar el problema de los años con un cero delante de la cifra significativa, deberías solucionarlo apelando a dos cuestiones:
- La visual que puedes resolver dándole formato personalizado. Esto es:
"Formato"
"Celda"
Elegir en la solapa "Número" la categoría "Personalizado" y
En la casilla superior, tipear:
00
OK para asignar este formato.
- Búsqueda en función de NUM-FICHA y AÑO-FICHA:
Ten en cuenta que la función buscarv trabaja sobre 1 único campo de identificación ubicado en la primer columna de la base a buscar. Por lo tanto, si deseas hacer una búsqueda en función de dos criterios, deberás crear un nuevo campo con la concatenación de estos dos.
Así, en tu Hoja1, deberás insertar una columna e introducirle una fórmula como la siguiente:
=B2&"-"&C2
Así obtendrás: 1200-1
Y siendo un campo auxiliar, no es necesario darle formato a este para que quede 1200-01
Así, con un par de retoques, tu fórmula funcionaría. Considerando el campo insertado a la base original, ella debería ser:
=SI(O(ESBLANCO($A2);ESBLANCO($B2));"";BUSCARV($A2&"-"&$B2;Hoja1!$A$2:$G$197;4;0))
Nota el uso de referencias semi-absolutas respect a la columna, que permite copiar esta fórmula hacia la derecha y cambiando el ;4; por los números sucesivos y obtener -así- los datos de APELLIDO1 | APELLIDO2 | DNI
La segunda parte de tu pregunta, también funcionaría con un buscarv pero, como afirmas, será más complicada.
De todos modos la función =LARGO() con un condicional podría ayudar, al igual que identificar la presencia de un separador como "-".
Estoy trabajando en ello, pero me parece que lo que veo es sólo un ejemplo y que tu casuística puede ser aún mayor.
Sería útil para ambos, trabajar sobre un modelo cuasi final para evitar consideraciones erróneas posteriormente.
LAmentablemente no he encontrado en ninguna parte de tu extensa pregunta dónde enviarte el archivo...
Si quieres mantenerlo en privado, simplemente escríbeme a:
fejoal(en)hotmail.com
(Reemplaza "(en)" con "@".)
En principio, para evitar el problema de los años con un cero delante de la cifra significativa, deberías solucionarlo apelando a dos cuestiones:
- La visual que puedes resolver dándole formato personalizado. Esto es:
"Formato"
"Celda"
Elegir en la solapa "Número" la categoría "Personalizado" y
En la casilla superior, tipear:
00
OK para asignar este formato.
- Búsqueda en función de NUM-FICHA y AÑO-FICHA:
Ten en cuenta que la función buscarv trabaja sobre 1 único campo de identificación ubicado en la primer columna de la base a buscar. Por lo tanto, si deseas hacer una búsqueda en función de dos criterios, deberás crear un nuevo campo con la concatenación de estos dos.
Así, en tu Hoja1, deberás insertar una columna e introducirle una fórmula como la siguiente:
=B2&"-"&C2
Así obtendrás: 1200-1
Y siendo un campo auxiliar, no es necesario darle formato a este para que quede 1200-01
Así, con un par de retoques, tu fórmula funcionaría. Considerando el campo insertado a la base original, ella debería ser:
=SI(O(ESBLANCO($A2);ESBLANCO($B2));"";BUSCARV($A2&"-"&$B2;Hoja1!$A$2:$G$197;4;0))
Nota el uso de referencias semi-absolutas respect a la columna, que permite copiar esta fórmula hacia la derecha y cambiando el ;4; por los números sucesivos y obtener -así- los datos de APELLIDO1 | APELLIDO2 | DNI
La segunda parte de tu pregunta, también funcionaría con un buscarv pero, como afirmas, será más complicada.
De todos modos la función =LARGO() con un condicional podría ayudar, al igual que identificar la presencia de un separador como "-".
Estoy trabajando en ello, pero me parece que lo que veo es sólo un ejemplo y que tu casuística puede ser aún mayor.
Sería útil para ambos, trabajar sobre un modelo cuasi final para evitar consideraciones erróneas posteriormente.
LAmentablemente no he encontrado en ninguna parte de tu extensa pregunta dónde enviarte el archivo...
Si quieres mantenerlo en privado, simplemente escríbeme a:
fejoal(en)hotmail.com
(Reemplaza "(en)" con "@".)
