BUSCAR en dos páginas

Hola Héctor:
Saludos y gracias por tu siempre amable ayuda.
Tengo una plantilla que extrae datos de la base de datos que es el "libro1",
pestañas "hoja4" y "hoja5",. como lo comentado en mi anterior exposición.
En dicha plantilla que sirve para hacer ofertas de precio, pedidos, etc...
pongo en la celda B29 y sucesivas (B30,B31...) las referencias que deseo
extraer los datos.
Dichas referencias las pongo a mano o dicho sea de paso, las copio
directamente de un combo box cuyo código me facilitaste hace ya algún tiempo
y que me funciona de maravilla.
Asi que en la fila 29 tengo B29 la referencia que pongo a mano, en
D29(empaque) : buscarv(b29;basehoja4;4;falso) - en
E29(descripción):buscarv(b29;basehoja4;3;falso)en F29(precio
tarifa):buscarv(b29;basehoja4;7;falso) y con datos de cantidad y cálculos de
descuentos y netos completo la plantilla(lo mismo para las siguientes
filas 30-56) .
En otras celdas que no vienen al caso extraigo el resto de datos que
necesito...
El rango pues de basehoja4 podría ser de B2:M65536 o lo que me aconsejes
pues en la hoja4 llega hasta la fila 50000 y en hoja5 hasta 48085.
Te expongo a continuación las columnas con sus titulos.



1 Columna A Referencia
2 Columna B Código
3 Columna C Descripción
4 Columna D Empaque
5 Columna E Grupo estadistico
6 Columna F Tipo
7 Columna G Precio tarifa
8 Columna H Neto venta
9 Columna I Coste
10 Columna J Beneficio
11 Columna K Margen sobre venta
12 Columna L Margen sobre coste
13 Colymna M Precio Lista x 100 u.


Saludos de nuevo y gracias.
José Rafael


"Héctor Miguel" escribió en el mensaje
news:

hola, José Rafael !

en tanto comentas algunos de los detalles que se te quedaro "en el
tintero"
(como donde pones el dato que se busca, los rangos de busqueda y la
columna a devolver)...
si suponemos que el dato buscado esta en la celda A1 y su referencia esta
en la columna A de las dos (o mas) hojas...
y que se trata de obtener el dato correspondiente a dicha fila en su
columna 'C' (de "X" hoja donde sea que se encuentre)...

1) pones el dato que se busca (p.e.) en la celda A1
2) escribe los nombres de las 2 (o mas) hojas, p.e. en el rango 'B1:B2'
3) introduce la siguiente formula "matricial" en C1 (ya sabes)... -> se
debe terminar con {ctrl}+{shift}+{enter}

=buscarv(a1,indirecto("'"&indice(b1:b2,coincidir(verdadero,contar.si(indirecto("'"&b1:b2&"'!a:a"),a1)>0,0))&"'!a:c"),3,0)

solo toma en cuenta que mi sistema usa como separador de argumentos a la
coma ',' NO al punto y coma :D

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector

__ la consulta original __

Tengo una lista de precios de casi 100.000 articulos.
Cuando el software de mi empresa me la actualiza en excel, me abre un
libro nuevo "Libro1"... con 3 pestanas
en la "hoja1" me indica los datos de la peticion que he hecho al
"programa madre" de la empresa
p.ej. "Tarifa de precios al 29-2-08, solicitada por E40 y fecha 4-3-08"
las hojas 2 y 3 las deja en blanco y me abre la "hoja4" donde con una
primera fila de encabezados
llega hasta la fila 50.000 extrayendo los articulos con sus
caracteristicas por columna.
(Columna A=Referencia; Columna B=Nombre;Columna C=Empaque, etc, así hasta
la columna M).
Como el listado va mas alla de los 65536 filas, nombra otra "hoja5" que
con el mismo encabezado en la primera fila
continua extrayendo articulos hasta completar el listado que en este caso
llega hasta la fila 48.085 de esta "hoja5".
Con una plantilla propia y sobre todo con BuscarV, "coloco" los datos
donde los necesito
pero no se como seria la formula para que busque en la primera "hoja4" y
si encuentra la referencia extraiga datos
y si no la encuentra pase a la segunda "hoja5" para seguir buscando.
Las referencias de articulos son unicas es decir irrepetibles...

hola, Jose !

1) que significa (o a que hoja y rangos se refiere) "basehoja4" en las formulas que expones ?
asumiendo que se refiere a rangos nombrados... como solucionas la busqueda en la hoja5 ?

2) ya probaste con la formula de la propuesta ?

3) que es lo que (realmente) necesitas ?
a) quieres depositar la formula (matricial) por codigo en las celdas "correpondientes" ?
b) si va a ser por codigo, prefieres depositar solo el resultado en lugar de llenar un libro con formulas matriciales ?

(creo que sigue por ahi algun detalle "en el tintero")

saludos,
hector.

Tengo una plantilla que extrae... de la base de datos... "libro1", pestanas "hoja4" y "hoja5"...
... pongo en la celda B29 y sucesivas (B30,B31...) las referencias que deseo extraer los datos.
... en D29(empaque): buscarv(b29;basehoja4;4;falso)
- en E29(descripcion): buscarv(b29;basehoja4;3;falso)
- en F29(precio tarifa): buscarv(b29;basehoja4;7;falso)... (lo mismo para las siguientes filas 30-56).
En otras celdas que no vienen al caso extraigo el resto de datos que necesito...
El rango pues de basehoja4 podria ser de B2:M65536 o lo que me aconsejes
pues en la hoja4 llega hasta la fila 50000 y en hoja5 hasta 48085.
Te expongo a continuacion las columnas con sus titulos.
1 Columna A Referencia
2 Columna B Codigo
3 Columna C Descripcion
4 Columna D Empaque
5 Columna E Grupo estadistico
6 Columna F Tipo
7 Columna G Precio tarifa
8 Columna H Neto venta
9 Columna I Coste
10 Columna J Beneficio
11 Columna K Margen sobre venta
12 Columna L Margen sobre coste
13 Colymna M Precio Lista x 100 u.

__ la consulta anterior __

en tanto comentas algunos de los detalles que se te quedaron "en el tintero"
(como donde pones el dato que se busca, los rangos de busqueda y la columna a devolver)...
si suponemos que el dato buscado esta en la celda A1 y su referencia esta en la columna A de las dos (o mas) hojas...
y que se trata de obtener el dato correspondiente a dicha fila en su columna 'C' (de "X" hoja donde sea que se encuentre)...

1) pones el dato que se busca (p.e.) en la celda A1
2) escribe los nombres de las 2 (o mas) hojas, p.e. en el rango 'B1:B2'
3) introduce la siguiente formula "matricial" en C1 (ya sabes)... -> se debe terminar con {ctrl}+{shift}+{enter}

=buscarv(a1,indirecto("'"&indice(b1:b2,coincidir(verdadero,contar.si(indirecto("'"&b1:b2&"'!a:a"),a1)>0,0))&"'!a:c"),3,0)

solo toma en cuenta que mi sistema usa como separador de argumentos a la coma ',' NO al punto y coma :D

__ la consulta original __
Tengo una lista de precios de casi 100.000 articulos.
Cuando el software de mi empresa me la actualiza en excel, me abre un libro nuevo "Libro1"... con 3 pestanas
en la "hoja1" me indica los datos de la peticion que he hecho al "programa madre" de la empresa
p.ej. "Tarifa de precios al 29-2-08, solicitada por E40 y fecha 4-3-08"
las hojas 2 y 3 las deja en blanco y me abre la "hoja4" donde con una primera fila de encabezados
llega hasta la fila 50.000 extrayendo los articulos con sus caracteristicas por columna.
(Columna A=Referencia; Columna B=Nombre;Columna C=Empaque, etc, asi hasta la columna M).
Como el listado va mas alla de los 65536 filas, nombra otra "hoja5" que con el mismo encabezado en la primera fila
continua extrayendo articulos hasta completar el listado que en este caso llega hasta la fila 48.085 de esta "hoja5".
Con una plantilla propia y sobre todo con BuscarV, "coloco" los datos donde los necesito
pero no se como seria la formula para que busque en la primera "hoja4" y si encuentra la referencia extraiga datos
y si no la encuentra pase a la segunda "hoja5" para seguir buscando.
Las referencias de articulos son unicas es decir irrepetibles...

El rango "basehoja4" correspondería a A2:M65536 de la hoja4(aunque en la
hoja4 solo se llega hasta la fila 50.000... ¿se podría crear un rango
dinámico hasta la última fila con datos?.
El rango "basehoja5" correspondería a A2:M65536 de la hoja5 (aunque en la
hoja5 solo se llega hasta la fila 48.085... como en la otra...¿se podría
crear un rango dinámico hasta la última fila con datos?.
Una vez establecidos los rangos se trataría de extraer los datos de ese
rango escribiendo en la hoja "plantilla" en B29 una referencia cualquiera de
las 98.085 existentes, que como digo es irrepetible y en las celdas a la
derecha de esa misma fila que se "coloquen" (mediante códiog o fórmulas) los
datos correspondientes a las columnas indicadas de la fila de esa misma
referencia en la base de datos. ¿complicado?.
He pensado en una fórmula mas o menos así:
en D29 si(BUSCARV(b29;basehoja4;4;falso)=0;BUSCARV(b29;basehoja5;4;falso) .
o quizás con Y / O
en E29 si(BUSCARV(b29;basehoja4;3;falso)=0;BUSCARV(b29;basehoja5;3;falso) .
o quizás con Y / O
en F29 si(BUSCARV(b29;basehoja4;7;falso)=0;BUSCARV(b29;basehoja5;7;falso) .
o quizás con Y / O
y así sucesivamente...
por tanto, que necesito:
Por un lado establecer el rango dinámico cada vez que obtenga un nuevo libro
de la tarifa de precios ...
De otro lado, la fórmula matricial o no, ó por código que me extraiga en la
hoja "plantilla" los datos que necesito por fila cuando ponga en B29, B30,
B31... etc
la referencia del articulo.
Desgraciadamente no he podido probar todavía por falta de tiempo la primera
fórmula matricial que me distes. No sé si está adapatada a estos nuevos
datos que te doy hoy. En fin, Héctor que propongas lo que quieras o sea
mejor según tu punto de vista para conseguir los objetivos que necesito
(rellenar la "plantilla", rangos dinamicos...).
Saludos y gracias
José Rafael



"Héctor Miguel" escribió en el mensaje
news:

hola, Jose !

1) que significa (o a que hoja y rangos se refiere) "basehoja4" en las
formulas que expones ?
asumiendo que se refiere a rangos nombrados... como solucionas la
busqueda en la hoja5 ?

2) ya probaste con la formula de la propuesta ?

3) que es lo que (realmente) necesitas ?
a) quieres depositar la formula (matricial) por codigo en las celdas
"correpondientes" ?
b) si va a ser por codigo, prefieres depositar solo el resultado en
lugar de llenar un libro con formulas matriciales ?

(creo que sigue por ahi algun detalle "en el tintero")

saludos,
hector.

Tengo una plantilla que extrae... de la base de datos... "libro1",
pestanas "hoja4" y "hoja5"...
... pongo en la celda B29 y sucesivas (B30,B31...) las referencias que
deseo extraer los datos.
... en D29(empaque): buscarv(b29;basehoja4;4;falso)
- en E29(descripcion): buscarv(b29;basehoja4;3;falso)
- en F29(precio tarifa): buscarv(b29;basehoja4;7;falso)... (lo mismo para
las siguientes filas 30-56).
En otras celdas que no vienen al caso extraigo el resto de datos que
necesito...
El rango pues de basehoja4 podria ser de B2:M65536 o lo que me aconsejes
pues en la hoja4 llega hasta la fila 50000 y en hoja5 hasta 48085.
Te expongo a continuacion las columnas con sus titulos.
1 Columna A Referencia
2 Columna B Codigo
3 Columna C Descripcion
4 Columna D Empaque
5 Columna E Grupo estadistico
6 Columna F Tipo
7 Columna G Precio tarifa
8 Columna H Neto venta
9 Columna I Coste
10 Columna J Beneficio
11 Columna K Margen sobre venta
12 Columna L Margen sobre coste
13 Colymna M Precio Lista x 100 u.

__ la consulta anterior __

en tanto comentas algunos de los detalles que se te quedaron "en el
tintero"
(como donde pones el dato que se busca, los rangos de busqueda y la
columna a devolver)...
si suponemos que el dato buscado esta en la celda A1 y su referencia
esta en la columna A de las dos (o mas) hojas...
y que se trata de obtener el dato correspondiente a dicha fila en su
columna 'C' (de "X" hoja donde sea que se encuentre)...

1) pones el dato que se busca (p.e.) en la celda A1
2) escribe los nombres de las 2 (o mas) hojas, p.e. en el rango 'B1:B2'
3) introduce la siguiente formula "matricial" en C1 (ya sabes)... -> se
debe terminar con {ctrl}+{shift}+{enter}

=buscarv(a1,indirecto("'"&indice(b1:b2,coincidir(verdadero,contar.si(indirecto("'"&b1:b2&"'!a:a"),a1)>0,0))&"'!a:c"),3,0)

solo toma en cuenta que mi sistema usa como separador de argumentos a la
coma ',' NO al punto y coma :D

__ la consulta original __
Tengo una lista de precios de casi 100.000 articulos.
Cuando el software de mi empresa me la actualiza en excel, me abre un
libro nuevo "Libro1"... con 3 pestanas
en la "hoja1" me indica los datos de la peticion que he hecho al
"programa madre" de la empresa
p.ej. "Tarifa de precios al 29-2-08, solicitada por E40 y fecha 4-3-08"
las hojas 2 y 3 las deja en blanco y me abre la "hoja4" donde con una
primera fila de encabezados
llega hasta la fila 50.000 extrayendo los articulos con sus
caracteristicas por columna.
(Columna A=Referencia; Columna B=Nombre;Columna C=Empaque, etc, asi
hasta la columna M).
Como el listado va mas alla de los 65536 filas, nombra otra "hoja5" que
con el mismo encabezado en la primera fila
continua extrayendo articulos hasta completar el listado que en este
caso llega hasta la fila 48.085 de esta "hoja5".
Con una plantilla propia y sobre todo con BuscarV, "coloco" los datos
donde los necesito
pero no se como seria la formula para que busque en la primera "hoja4"
y si encuentra la referencia extraiga datos
y si no la encuentra pase a la segunda "hoja5" para seguir buscando.
Las referencias de articulos son unicas es decir irrepetibles...

hola, José Rafael !

__ 1 __

El rango "basehoja4" corresponderia a A2:M65536 de la hoja4 (aunque en la hoja4 solo se llega hasta la fila 50.000...
se podria crear un rango dinamico hasta la ultima fila con datos?.

1) si, aunque no lo creo necesario, puedes buscar en las columnas "enteras" p.e. =buscarv(dato_buscado,a:m,5,falso)
-> considerando que el dato_buscado es "localizable" en la columna A (o en la primera del rango de busqueda)

__ 2 __

El rango "basehoja5" correspondería a A2:M65536 de la hoja5 (aunque en la hoja5 solo se llega hasta la fila 48.085...
como en la otra... se podria crear un rango dinamico hasta la ultima fila con datos?.

2) ver punto anterior

__ 3 __

Una vez establecidos los rangos se trataria de extraer los datos de ese rango escribiendo en la hoja "plantilla" en B29
una referencia cualquiera de las 98.085 existentes, que como digo es irrepetible
y en las celdas a la derecha de esa misma fila que se "coloquen" (mediante codiog o formulas)
los datos correspondientes a las columnas indicadas de la fila de esa misma referencia en la base de datos. complicado?.

3) no lo creo :D

__ 4 __

He pensado en una formula mas o menos asi:
en D29 si(BUSCARV(b29;basehoja4;4;falso)=0;BUSCARV(b29;basehoja5;4;falso) . o quizas con Y / O ...
en E29 si(BUSCARV(b29;basehoja4;3;falso)=0;BUSCARV(b29;basehoja5;3;falso) . o quizas con Y / O ...
en F29 si(BUSCARV(b29;basehoja4;7;falso)=0;BUSCARV(b29;basehoja5;7;falso) . o quizas con Y / O ...
y asi sucesivamente...

4) otras alternativas... usar el si-condicional "preguntando" primero si existe "el dato en la hoja4... y hacer la busqueda
si no... "preguntar" si existe "el dato" en la hoja5... y hacer la busqueda
si no... "avisar" que el dato buscado NO existe en ninguna de la hojas (p.e.)

=si(contar.si(hoja4!a:a;b29);buscarv(b29;hoja4!a:b;2;0);si(contar.si(hoja5!a:a;b29);buscarv(b29;hoja5!a:b;2;0);"no existe"))

OJO: nota que el contar.si(... usa la columna A (para preguntar si existe) y el buscarv(... usa A:B y devuelve B -columna 2-
independientemente de usar "variables" para rescatar columnas "auto-ajustable" (no tiene por que ser fija en 2, 3, etc.)

__ 5 __

por tanto, que necesito:
Por un lado establecer el rango dinamico cada vez que obtenga un nuevo libro de la tarifa de precios ...

5) prueba haciendo la busqueda en las columnas "enteras" sin indicar rangos estaticos ni dinamicos (ver el punto 1)

__ 6 __

De otro lado, la formula matricial o no, o por codigo que me extraiga en la hoja "plantilla"
los datos que necesito por fila cuando ponga en B29, B30, B31... etc. la referencia del articulo.

6) haz pruebas con los "ejemplos" de formula del punto 4 anterior

__ 7 __

Desgraciadamente no he podido probar todavia por falta de tiempo la primera formula matricial que me distes.
No se si está adapatada a estos nuevos datos que te doy hoy.
En fin... que propongas lo que quieras o sea mejor segun tu punto de vista para conseguir los objetivos que necesito
(rellenar la "plantilla", rangos dinamicos...)...

7) decidir cual alternativa pudiera ser mejor, si combinar contar.si / buscarv o usar las formulas "matriciales"...
(seguramente) dependera de otro tipo de factores (p.e.)
- en cuantas celdas necesitas rescatar informacion sin saber si el dato buscado existe en una hoja u otra ?
- confirma/decide/... si serian formulas "por codigo" o prefieres usarlas "en manual" -???-

saludos,
hector.

Punto 7
Son 31 posiciones de celdas donde poner las referencias (B29:B60) para
rescatar en las columnas D,E,F,G de la misma fila los datos
correspopndientes. (por cierto, a veces solo se utilizan una parte de la
plantilla, p.e. de B29 a B35, resto en blanco)

Si me pasas un código lo probaré por si es mas rápido y mas práctico, sino,
utilizaré la fórmula manual.
Gracias una vez mas por tu tiempo y ayuda
Saludos cordiales
José Rafael


"Héctor Miguel" escribió en el mensaje
news:

hola, José Rafael !

__ 1 __

El rango "basehoja4" corresponderia a A2:M65536 de la hoja4 (aunque en la
hoja4 solo se llega hasta la fila 50.000...
se podria crear un rango dinamico hasta la ultima fila con datos?.

1) si, aunque no lo creo necesario, puedes buscar en las columnas
"enteras" p.e. =buscarv(dato_buscado,a:m,5,falso)
-> considerando que el dato_buscado es "localizable" en la columna A (o
en la primera del rango de busqueda)

__ 2 __

El rango "basehoja5" correspondería a A2:M65536 de la hoja5 (aunque en la
hoja5 solo se llega hasta la fila 48.085...
como en la otra... se podria crear un rango dinamico hasta la ultima fila
con datos?.

2) ver punto anterior

__ 3 __

Una vez establecidos los rangos se trataria de extraer los datos de ese
rango escribiendo en la hoja "plantilla" en B29
una referencia cualquiera de las 98.085 existentes, que como digo es
irrepetible
y en las celdas a la derecha de esa misma fila que se "coloquen"
(mediante codiog o formulas)
los datos correspondientes a las columnas indicadas de la fila de esa
misma referencia en la base de datos. complicado?.

3) no lo creo :D

__ 4 __

He pensado en una formula mas o menos asi:
en D29 si(BUSCARV(b29;basehoja4;4;falso)=0;BUSCARV(b29;basehoja5;4;falso)
. o quizas con Y / O ...
en E29 si(BUSCARV(b29;basehoja4;3;falso)=0;BUSCARV(b29;basehoja5;3;falso)
. o quizas con Y / O ...
en F29 si(BUSCARV(b29;basehoja4;7;falso)=0;BUSCARV(b29;basehoja5;7;falso)
. o quizas con Y / O ...
y asi sucesivamente...

4) otras alternativas... usar el si-condicional "preguntando" primero si
existe "el dato en la hoja4... y hacer la busqueda
si no... "preguntar" si existe "el dato" en la hoja5... y hacer la
busqueda
si no... "avisar" que el dato buscado NO existe en ninguna de la hojas
(p.e.)


=si(contar.si(hoja4!a:a;b29);buscarv(b29;hoja4!a:b;2;0);si(contar.si(hoja5!a:a;b29);buscarv(b29;hoja5!a:b;2;0);"no
existe"))

OJO: nota que el contar.si(... usa la columna A (para preguntar si
existe) y el buscarv(... usa A:B y devuelve B -columna 2-
independientemente de usar "variables" para rescatar columnas
"auto-ajustable" (no tiene por que ser fija en 2, 3, etc.)

__ 5 __

por tanto, que necesito:
Por un lado establecer el rango dinamico cada vez que obtenga un nuevo
libro de la tarifa de precios ...

5) prueba haciendo la busqueda en las columnas "enteras" sin indicar
rangos estaticos ni dinamicos (ver el punto 1)

__ 6 __

De otro lado, la formula matricial o no, o por codigo que me extraiga en
la hoja "plantilla"
los datos que necesito por fila cuando ponga en B29, B30, B31... etc. la
referencia del articulo.

6) haz pruebas con los "ejemplos" de formula del punto 4 anterior

__ 7 __

Desgraciadamente no he podido probar todavia por falta de tiempo la
primera formula matricial que me distes.
No se si está adapatada a estos nuevos datos que te doy hoy.
En fin... que propongas lo que quieras o sea mejor segun tu punto de
vista para conseguir los objetivos que necesito
(rellenar la "plantilla", rangos dinamicos...)...

7) decidir cual alternativa pudiera ser mejor, si combinar contar.si /
buscarv o usar las formulas "matriciales"...
(seguramente) dependera de otro tipo de factores (p.e.)
- en cuantas celdas necesitas rescatar informacion sin saber si el dato
buscado existe en una hoja u otra ?
- confirma/decide/... si serian formulas "por codigo" o prefieres
usarlas "en manual" -???-

saludos,
hector.