Rangos Dinamicos

Prueba con la siguiente fórmula para Cuentas_Cheques:

ÞSREF(Hoja2!$A$13;;;CONTARA(Hoja2!$A$13:$A$65536))

Para los otros dos rangos dinámicos lo único que tendrías que hacer es cambiar la columna.
Un saludo.


Fernando Arroyo
MS MVP - Excel


"José A. Castrejón" escribió en el mensaje news:

Buenas Noches,
Tengo definidos 3 rangos
Importe_Cheques = $F13:$F1700
Cuentas_Cheques = $A13:$A1700
Status_Cheques = $G13:$G1700

En otra hoja(2) tengo sa siguiente formula
=SUMAPRODUCTO(--(Cuentas_Cheques=$A12),--(Status_Cheques="En
Transito")--(Importe_Cheques)

y funciona muy bien suma todos los cheques en transito de la cuenta que
digito en a12

Cambie los rangos a una forma dinamica

Importe_Cheques = DESREF(Hoja2!$F$12,0,0,CONTARA(Hoja2!$F:$F),1)
Cuentas_Cheques = DESREF(Hoja2!$A$12,0,0,CONTARA(Hoja2!$A:$A),1)
Status_Cheques = DESREF(Hoja2!$G$12,0,0,CONTARA(Hoja2!$G:$G),1)

Y la formula de resultado en hoja 1 me dice !VALOR!

Que tengo mal ?
Gracias por su ayuda

hola, José !

... definidos 3 rangos
Importe_Cheques = $F13:$F1700
Cuentas_Cheques = $A13:$A1700
Status_Cheques = $G13:$G1700
... hoja(2)... la... formula =SUMAPRODUCTO(--(Cuentas_Cheques=$A12),--(Status_Cheques="En Transito")--(Importe_Cheques)
Cambie los rangos a una forma dinamica
Importe_Cheques = DESREF(Hoja2!$F$12,0,0,CONTARA(Hoja2!$F:$F),1)
Cuentas_Cheques = DESREF(Hoja2!$A$12,0,0,CONTARA(Hoja2!$A:$A),1)
Status_Cheques = DESREF(Hoja2!$G$12,0,0,CONTARA(Hoja2!$G:$G),1)
Y la formula de resultado en hoja 1 me dice !VALOR! [...]

adicional a lo que te comenta Fernando... es probable que...
al momento de 're/definir' [los nombres de] los rangos de 'constantes' a 'dinamicos'...
'la formula' ya no puede 'comparar' matrices de 'la misma dimension' -?- [me explico]...
1) 'originalmente' los nombres hacian referencia al mismo numero [constante] de filas [13:1700]
2) 'ahora'... cada [nuevo] rango podria estar haciendo referencia a un numero de filas 'distinto' de los otros
-> la funcion contara() 'devuelve' el numero de filas 'NO vacias' ->dentro de cada una de las columnas<-
[seguramente] existe un numero diferente de celdas 'ocupadas' [o NO 'vacias'] en cada rango [A, G y F]
[con lo cual, la formula ya no 'encuentra' la misma constante [filas] en la dimension de las matrices] :-(

[creo que] lo 'mejor' seria determinar CUAL es la columna 'mandante' [aquella que resultara siempre la mas 'larga']
y 'desplazar' la referencia a 'los otros' nombres [digamos...] n_columnas -> con base en este rango 'basico'< - ;)
si suponemos que dicha columna sera [siempre] la columna 'A'... prueba con las siguientes 're/definiciones'...
[suponiendo que la columna 'A' esta basada SOLO en valores de texto]...
Cuentas_Cheques =desref(hoja2!$a$13,1,,coincidir(repetir("z",255),hoja2!$a:$a)-fila(hoja2!$a$13))
Importe_Cheques =desref(cuentas_cheques,,5)
Status_Cheques =desref(cuentas_cheques,,6)

si cualquier duda [o informacion adicional]... comentas?
saludos,
hector.

Gracias a ambos por sus respuestas, probe las 2 soluciones y no funcionan del
todo bien, sin embargo al ver como estaban formados los rangos dinamicos y
leer un poco mas sobre offset (desref en español), cambie algunos parametros
y parece que esta funcionando bien.
el final fue =desref($A$13,13,0,CONTARA($A13:$A65535)-1),1)
El problema que tenia con el anterior era que si eliminaba la linea 13
(conteniendo el primer registro) mediante un userform, se perdia el rango por
alguna razón y los resultados de sumaproducto me daban #VALOR#.
Mis datos comienzan en A13 y los titulos en A12.
saludos

"Héctor Miguel" escribió:

hola, José !

> ... definidos 3 rangos
> Importe_Cheques = $F13:$F1700
> Cuentas_Cheques = $A13:$A1700
> Status_Cheques = $G13:$G1700
> ... hoja(2)... la... formula =SUMAPRODUCTO(--(Cuentas_Cheques=$A12),--(Status_Cheques="En Transito")--(Importe_Cheques)
> Cambie los rangos a una forma dinamica
> Importe_Cheques = DESREF(Hoja2!$F$12,0,0,CONTARA(Hoja2!$F:$F),1)
> Cuentas_Cheques = DESREF(Hoja2!$A$12,0,0,CONTARA(Hoja2!$A:$A),1)
> Status_Cheques = DESREF(Hoja2!$G$12,0,0,CONTARA(Hoja2!$G:$G),1)
> Y la formula de resultado en hoja 1 me dice !VALOR! [...]

adicional a lo que te comenta Fernando... es probable que...
al momento de 're/definir' [los nombres de] los rangos de 'constantes' a 'dinamicos'...
'la formula' ya no puede 'comparar' matrices de 'la misma dimension' -?- [me explico]...
1) 'originalmente' los nombres hacian referencia al mismo numero [constante] de filas [13:1700]
2) 'ahora'... cada [nuevo] rango podria estar haciendo referencia a un numero de filas 'distinto' de los otros
-> la funcion contara() 'devuelve' el numero de filas 'NO vacias' ->dentro de cada una de las columnas<-
[seguramente] existe un numero diferente de celdas 'ocupadas' [o NO 'vacias'] en cada rango [A, G y F]
[con lo cual, la formula ya no 'encuentra' la misma constante [filas] en la dimension de las matrices] :-(

[creo que] lo 'mejor' seria determinar CUAL es la columna 'mandante' [aquella que resultara siempre la mas 'larga']
y 'desplazar' la referencia a 'los otros' nombres [digamos...] n_columnas -> con base en este rango 'basico'< - ;)
si suponemos que dicha columna sera [siempre] la columna 'A'... prueba con las siguientes 're/definiciones'...
[suponiendo que la columna 'A' esta basada SOLO en valores de texto]...
Cuentas_Cheques =desref(hoja2!$a$13,1,,coincidir(repetir("z",255),hoja2!$a:$a)-fila(hoja2!$a$13))
Importe_Cheques =desref(cuentas_cheques,,5)
Status_Cheques =desref(cuentas_cheques,,6)

si cualquier duda [o informacion adicional]... comentas?
saludos,
hector.

hola, José !

... probe las 2 soluciones y no funcionan del todo bien
... al... leer un poco mas sobre offset (desref en español), cambie algunos parametros y parece que esta funcionando bien.
el final fue =desref($A$13,13,0,CONTARA($A13:$A65535)-1),1)
El problema que tenia con el anterior era que si eliminaba la linea 13 (conteniendo el primer registro) mediante un userform
... perdia el rango por alguna razon y los resultados de sumaproducto me daban #VALOR#.
Mis datos comienzan en A13 y los titulos en A12

1) solo como un comentario adicional, [y segun entiendo] los argumentos de la funcion DesRef(ref,filas,columnas,alto,ancho)
tienen el siguiente 'significado' =>y pueden ser formulas y/o funciones 'combinadas'< ref = referencia a la celda inicial [o lo que seria la 'celda de partida']
filas = numero de filas que se desplazara [positivo->abajo, negativo->arriba, cero [u omitido]->la misma]
columnas = numero de columnas a desplazar [positivo->derecha, negativo->izquierda, cero/omitido->la misma]
alto = numero de filas a incluir [positivo->aumentar, negativo->disminuir, cero/omitido->las mismas]
ancho = numero de columnas a incluir [positivo->aumentar, negativo->disminuir, cero/omitido->las mismas]

2) la formula aplicada [segun los parametros y tal como los estas usando] 'significa':
ref [referencia 'inicial'] = $A$13
filas a desplazar = 13 [es decir... la primer celda del rango 'desplazado/re-definido' va a ser: $A$26] <= OJO
columnas a desplazar = 0 [o sea... 'la misma'] = 'A'
alto [filas a incluir] = CONTARA($A13:$A65535)-1 -> Si todas NO vacias... CONTARA 'devuelve' 65523 MENOS UNO
ancho [cols. a incluir] = 1
por lo tanto [suponiendo que todas las celdas estan NO vacias]... DESREF 'significa' [o 'devuelve']: "A26:A65522"

3) si los titulos empiezan en 'A12'... podrias [p.e.] modificar la formula propuesta en el mensaje anterior...
de -> Cuentas_Cheques =desref(hoja2!$a$13,1,,coincidir(repetir("z",255),hoja2!$a:$a)-fila(hoja2!$a$13))
a -> Cuentas_Cheques =desref(hoja2!$a$12,1,,coincidir(repetir("z",255),hoja2!$a:$a)-fila(hoja2!$a$12))
es decir... 'mover' la referencia inicial [o 'de partida'] de la fila 13 [que son datos] a la fila 12 [que son los titulos] ;)
una [posible] 'ventaja' de sustituir la funcion en el cuarto argumento -alto- de contara(...) a coincidir(repetir(...
[y suponiendo que en dicha columna SOLO habra valores de texto]... seria que...
podrias -incluso- dejar celdas vacias y la referencia [desplazada] estaria -siempre- 'apuntando' a la ultima celda con texto

4) ademas de lo anterior... al 'basar' los siguientes rangos a este 'nombre basico'...
Importe_Cheques =desref(cuentas_cheques,,5)
Status_Cheques =desref(cuentas_cheques,,6)
te estarias 'asegurando' que el numero de filas [inicio y fin] de todos los rangos 'dinamicos' sera siempre 'conicidente'

si cualquier duda... comentas?
saludos,
hector.