What Function Should I Use. Is this a "What If" Type of Argument?
 

I am looking for the type of formula that will return the values in a range
of cells based upon the value in one cell.

If the value in cell A1 is "1" in worksheet "First" then return a range of
values from 25 cells A1:A25 in worksheet "Second" and if the value in A1 is
"2" in worksheet "First" then return a range of values from 25 cells B1:B25
in worksheet "Second" and so on.

I want to be able to change a range of cell values to the right of the
primary cell based upon the value in the primary cell. If the value in this
cell is "1" then go to another worksheet and find the range of cell values
that will be copied to the right of primary cell.

I hope this is clear.

Thanks

What Function Should I Use. Is this a "What If" Type of Argument?
 

Maybe this idea is what you are looking for?
=VLOOKUP("a",second!a1:z25,a1+1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott R" <Scott wrote in message
...
I am looking for the type of formula that will return the values in a range
of cells based upon the value in one cell.

If the value in cell A1 is "1" in worksheet "First" then return a range of
values from 25 cells A1:A25 in worksheet "Second" and if the value in A1
is
"2" in worksheet "First" then return a range of values from 25 cells
B1:B25
in worksheet "Second" and so on.

I want to be able to change a range of cell values to the right of the
primary cell based upon the value in the primary cell. If the value in
this
cell is "1" then go to another worksheet and find the range of cell values
that will be copied to the right of primary cell.

I hope this is clear.

Thanks

What Function Should I Use. Is this a "What If" Type of Argument?
 

On Feb 9, 5:56 am, Scott R <Scott
wrote:
I am looking for the type of formula that will return the values in a range
of cells based upon the value in one cell.

If the value in cell A1 is "1" in worksheet "First" then return a range of
values from 25 cells A1:A25 in worksheet "Second" and if the value in A1 is
"2" in worksheet "First" then return a range of values from 25 cells B1:B25
in worksheet "Second" and so on.

Perhaps this will work for you:

indirect(choose(First!$A$1, "Second!$A$1:$A$25", "Second!$B$1:$B$5"))

If you have more than 29 ranges, or simply because you want to,
replace CHOOSE with VLOOKUP into a table that returns the ranges in
string form.

What Function Should I Use. Is this a "What If" Type of Argument?
 

PS....

On Feb 9, 9:20*am, I wrote:
Perhaps this will work for you:
indirect(choose(First!$A$1, "Second!$A$1:$A$25", "Second!$B$1:$B$5"))

(Of course, I had a typo: $B$5 should be $B$25.)

If the ranges are always adjacent columns and the same number of rows,
the following might work for you:

offset(Second!$A$1:$A$25, 0, First!$A$1-1)