Conocer la referencia de una celda mediante fórmula

hola, Roberto !

serviria si expones la formula que utilizas para definir el rango dinamico (de antemano)...
si estas usando la funcion contara (o counta en ingles) para contar las filas...
deberas cambiarla por coincidir (match si en ingles) para localizar "la ultima fila" con numeros

saludos,
hector.

__ OP __

Necesito saber si existe alguna forma de conocer la referencia de una celda mediante una formula.
En estos momentos poseo una columna que contiene celdas vacias y celdas llenas
lo que estoy haciendo es que paso a traves de una formula los datos de esa columna a una nueva columna
(Columna de destino) en otra hoja en la cual me los ordena y elimina las celdas vacias intermedias.
En la columna en la cual tengo celdas vacias (llamemosla columna original), utilice un rango dinamico
pero este rango se detiene cuando consigue algunas celdas vacias y no selecciona hasta la ultima celda ocupada
porque en el medio pueden existir muchas celdas vacias...

Gracias por contestar tan rápido

La formula que estoy usando para definir el rango es

DESREF(hoja1!$U$7;0;0;CONTARA(hoja1!$U:$U);1)

saludos

Roberto

hola, Roberto !

La formula que estoy usando para definir el rango es
DESREF(hoja1!$U$7;0;0;CONTARA(hoja1!$U:$U);1)

si buscas (o existen) solo numeros en la columna U... prueba con:
=desref(hoja1!$u$7;;;coincidir(9e307;hoja1!$u:$u);)

saludos,
hector.

On 26 ago, 18:24, "Héctor Miguel"
wrote:

hola, Roberto !

> La formula que estoy usando para definir el rango es
> DESREF(hoja1!$U$7;0;0;CONTARA(hoja1!$U:$U);1)

si buscas (o existen) solo numeros en la columna U... prueba con:
=desref(hoja1!$u$7;;;coincidir(9e307;hoja1!$u:$u);)

saludos,
hector.

La fórmula funciona de maravilla, pero podrías explicarme que
significa o como se utiliza 9e307, es primera vez que lo veo
Gracias

hola, Roberto !

si buscas (o existen) solo numeros en la columna U... prueba con:
=desref(hoja1!$u$7;;;coincidir(9e307;hoja1!$u:$u);)

La formula funciona de maravilla, pero podrías explicarme que significa o como se utiliza 9e307, es primera vez que lo veo

lo que sigue, es una completa explicacion ofrecida por KL a consulta similar +/- en mayo de 2006

saludos,
hector.

== consulta original =>...saber porque se utiliza 9e+307

ya que busque la ayuda sobre la funcion indice y sobre la funcion coincidir pero no informa nada sobre esto.

== la respuesta de KL =Esta solucion se basa en un comportamiento no documentado de las funciones de busqueda aproximada
(COINCIDIR con el 3r parametro 1, -1,VERDADERO o vacio; BUSCARV y BUSCARH con el 4o parametro 1,VERDADERO u vacio; BUSCAR).
Al no encontrar el valor numerico buscado dichas funciones devueleven el ultimo valor numerico en el rango de busqueda.
Lo mismo ocurre con las cadenas de texto - si el texto buscado no se encuentra la funcion devolvera el ultimo texto del rango de busqueda.
Aprovechando este comportamiento, se utilizan los valores que por definicion son imposibles de encontrar en el rango buscado, p.ej.:

Para encontrar el ultimo valor numerico usamos 9E307 que es el numero que se aproxima al numero maximo posible en Excel: 9,99999999999999E+307:

=BUSCAR(9E307;A1:A10)
=BUSCARV(9E307;A1:A10;1)
=BUSCARV(9E307;A1:A10;1;1)
=BUSCARV(9E307;A1:A10;1;VERDADERO)
=BUSCARH(9E307;A1:Z1;1)
=BUSCARH(9E307;A1:Z1;1;1)
=BUSCARH(9E307;A1:Z1;1;VERDADERO)
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;1))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;-1))
=INDICE(A1:A10;COINCIDIR(9E307;A1:A10;VERDADERO))

Para encontrar el ultimo valor de texto usamos algo como REPETIR("z";255)
o simplemente "zzzzzz" que es un valor que se supone que no encontraremos un valor alfabeticamente mayor en nuestro rango de busqueda:

=BUSCAR(REPETIR("z";255) ;A1:A10)
=BUSCARV(REPETIR("z";255) ;A1:A10;1)
=BUSCARV(REPETIR("z";255) ;A1:A10;1;1)
=BUSCARV(REPETIR("z";255) ;A1:A10;1;VERDADERO)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1;1)
=BUSCARH(REPETIR("z";255) ;A1:Z1;1;VERDADERO)
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;1))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;-1))
=INDICE(A1:A10;COINCIDIR(REPETIR("z";255) ;A1:A10;VERDADERO))

Nota: Obviamente, el metodo de la busqueda del ultimo valor de texto anterior solo funciona
en las versiones de Excel que utilizan el alfabeto cuya ultima letra es "z" (por ejemplo en las versiones cirilicas la busqueda de la "z" fallaria).

Saludos,
KL