address command for a range of cells, not one cell

[Guest]

address comand returns value of a single cell, by giving the cekk address as
text input.
Is it possible to return a range of cells, instead of one vell either by
address or any other command?
In the following formula:
VLOOKUP($A1239;[runs_input.xls]run1!$G$4:$G$145;1;0)
I want to give [runs_input.xls]run1!$G$4:$G$145 range,
as an input so that I can copy run1 easily from run1 to run50.

 

[Dave Peterson]

As long as Runs_Input.xls is going to be open, you could use =indirect().

=VLOOKUP($A1239;indirect("[runs_input.xls]run"&row(a1)&"!$G$4:$G$145";1;0)
and drag down.

But this will break as soon as runs_input.xls is closed (and excel
recalculates).

If this is a one time thing, you can build a formula that helps build the
formula:

="$$$$$=vlookup($A1239;[runs_input.xls]run" & row(a1) & "!$G$4:$G$145;1;0)"
drag down the column.

Now select that range and
edit|copy
edit|paste special|values

Now with that range still selected
edit|replace
what: $$$$$=
with: =
replace all

Do a test on a short list (run1, run2, run3). If you make a mistake in your
formula, you may end up dismissing 50 dialog boxes trying to find the correct
file.

address comand returns value of a single cell, by giving the cekk address as
text input.
Is it possible to return a range of cells, instead of one vell either by
address or any other command?
In the following formula:
VLOOKUP($A1239;[runs_input.xls]run1!$G$4:$G$145;1;0)
I want to give [runs_input.xls]run1!$G$4:$G$145 range,
as an input so that I can copy run1 easily from run1 to run50.