Formula que consulte datos de diferentes hojas

8 envíos / 0 nuevos
Último envío
argacha
Imagen de argacha
Offline
última acción: Hace 1 mes 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntos
alta: 10/01/2007 - 08:28
Puntos: 1692
Formula que consulte datos de diferentes hojas

No estoy seguro que el título sea el correcto pero...

Lo que quiero es que una hoja excel, consulte unos determinados datos de otrad hojas del mismo libro. El problema mas gordo es que según pasa el tiempo voy añadiendo hojas nuevas al libro. Me explico, Cada hoja es una medición, esta medidicón lelva una fecha y una serie de datos en columnas. La primera es una profundidad y las segunda y tercera los datos medidos.

Lo que quiero es tener una hoja en ese libro que cada vez que añada una nueva medición( hoja) me consulte de esa nueva hoja la fecha y los datos de unas determiandas profundidades.

Subo un libro parecido para ilustrar mi ejemplo.

 

 

AdjuntoTamaño
File Libro1.xlsx16.91 KB
pacomegia
Imagen de pacomegia
Offline
última acción: Hace 1 día 15 horas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 8885
Re: Formula que consulte datos de diferentes hojas

puedes utilizar la función INDIRECTO para calcular la fórmula con un nombre de hoja que puedes poner en una celda.

Lo que no tengo claro es cómo conseguir que Excel detecte automáticamente que hay nuevas hojas y las incluya en el resumen sin utilizar macros.

te adjunto un ejemplo de la función indirecto.

esto es lo que necesitas:

lo necesitas así

he añadido una columna a la izquierda para poner el nombre de la hoja de la que proceden los datos:

nombres de las hojas

Supongo que los datos están en la misma posición en todas las hojas. para facilitar posteriores cambios, he escrito las celdas a las que hace referencia cada valor en la fila superior. Ya te digo que he supuesto que todo está en el mismo sitio y mi fórmula será simplemente referieme a esa celda, en otro caso la fórmula será diferente.

posiciones fijas para cada hoja

 

Ahora lo que hago es utilizar la función INDIRECTO para que busque (por ejemplo para el primer dato) en la hoja de nombre "Hoja3" (en la columan primera) la celda B2 (es la celda que le he indicado en la columna B)

Utilizo referencias relativas y absolutas para que me sirva la misma fórmula:

en condiciones normales sería algo así:

=Hoja3!B2

convertido a función indirecto, podría quedar algo así (he puesto comillas simples por si el nombre de la hoja tiene espacios por ejemplo 'hoja 1'!B2)

=INDIRECTO("'" & $ A6 & "'!" & B$1)

ya sólo queda poner esta misma fórmula en toda la tabla, y crear una tabla para que al añadir un nuevo nombre de hoja automáticamente se extiendan las fórmulas.

insertar una tabla para que las fórmulas se extiendan automáticamente

adjunto el archivo tal y como ha quedado.

------
Ya sé Excel, pero necesito más

AdjuntoTamaño
File variasHojas.xlsx20.11 KB

 

Data Tools Suite
datos y tablas con Excel

argacha
Imagen de argacha
Offline
última acción: Hace 1 mes 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntos
alta: 10/01/2007 - 08:28
Puntos: 1692
Re: Formula que consulte datos de diferentes hojas

Joer!!

qué bueno y qué fácil es añadir ahora datos... lo aplicaré a mis hojas y ya te contaré...

¿podríamos añadir una macro que se ejecute al abrir la hoja y lea todas las hojas del libro y me traiga las que se llaman "lectura_xx"?? así yo pondría "Lectura01" a una hoja "Lectura02" a la siguiente y si añado una que se llame "Lectura03" se actualizaría la tabla.

¿cómo se haría?

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 1 día 15 horas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 8885
Re: Formula que consulte datos de diferentes hojas

para contar las hojas, puedes utilizar la propiedad

thisworkbook.Worksheets.Count

también puedes recorrer todas las hojas e ir leyendo sus nombres con un bucle que pase por todas ellas.

algo así:

Dim Hoja As Worksheet
For Each Hoja In ThisWorkbook.Worksheets
'lo que sea con  Hoja.Name
Next

------
Ya sé Excel, pero necesito más

 

Data Tools Suite
datos y tablas con Excel

argacha
Imagen de argacha
Offline
última acción: Hace 1 mes 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntos
alta: 10/01/2007 - 08:28
Puntos: 1692
Re: Formula que consulte datos de diferentes hojas

Estupendo,ya tengo mis libros como me has dicho... pero... pero también tengo jefe y...

necesito poder selecionar diferentes profundidades de forma mas sencilla. Os cuento. De la hoja que enviaste, Paco, imaginate que quiero cambiar la primera profundidad, de 2 pasarla a 3. Ahora tengo que ir a la hoja, ver cual es la fila de esa profundidad y traerla a la fila 1. Luego debo ir a la celra C4 para indicar la profundidad nueva para que en gráfico salga la nueva profundidad.

Lo que me gustaría es que si cambio la celda C4 y pongo una nueva profundidad se cambien los valores correspondientes de la primera fila c1 d1. O bien alrevés si cambio los valores de la fila 1, veo la profundidad de la medida...

no sé si me he explicado pero seguro que me habéis entendido

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 1 día 15 horas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 8885
COINCIDIR

puedes utilizar la función COINCIDIR para encontrar la posición de ese valor que buscas dentro de una matriz.

la idea de esta función es que obtiene la posición del valor buscado. por ejemplo, el 2 está en la quinta celda de la columna de profundidades.

en tu caso como las profundidades están ordenadas, si no se introduce un valor exacto, puedes obtener la posición del valor inmediatamente inferior, es decir, que si quieres la profundidad 2.15 obtendrías la posición del 2.

luego utiliza esta posición que has obtenido para "calcular" la posición de la celda que emplearás en la función INDIRECTO.

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

Jose Martin Cam...
Imagen de Jose Martin Camacho Castro
Offline
última acción: Hace 4 meses 5 días
alta: 17/02/2018 - 23:19
Puntos: 0
Agradecer por tan excelente solución

Buenas a Todos y Todas los miembros de este Foro y muy agradecido por la solución de la función Indirecto, me soluciono un problema con un Libro de Control de Vacaciones y Resumen.

Espero que en algún momento pueda colaborar en alguna solución.

Muchas Gracias

José Martín Camacho Castro

San José Costa Rica

 

José Martín Camacho Castro

Soporte Técnico

jmartincc@gmail.com

Francisco (no verificado)
Imagen de Francisco
CONSULTA

Buneas tardes, por favor podrían ayudarme con saber como puedo usar la funcion indirecto con la formula sumar.si y obtener la informacion de otras hojas? (es para hacer un concentrado usando un criterio per con hojas de diferentes años.