Use a cell's value as row number for another cell in Excel

[Guest]

Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the
reference for the row value for a different series of cells (D2:F4). For
example:
Column A B C D E F
Row 1 1000 30 170 1 2 3
2 1500 60 190
3 1200 20 175
I want to get the following cells to equal:
D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1);
E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2);
F2=1200 (In other words: F2 = A3); F3=20 (In other words: F2 = B3);
....
F4=175 (In other words: F4 = C3);

Hope the explanation makes sense....I'd appreciate any help!
D. Hay

 

[Jason Morin]

Your example is somewhat confusing, but use this in D2
and fill to F2:

=OFFSET($A$1,COLUMN()-4,)

to pull in A1 to A3.

HTH
Jason
Atlanta, GA

 

[Guest]

You can use VBA to do this, but it might be a little clumsy.

For example, to fill D2 and E2, you need the code:

Range("D2") = Range("a" & Range("d1").value)
Range("E2") = Range("a" & Range("e1").value)
.... and so on for the other cells.

I noticed that what you are essentially doing is transposing the data, the
other way to do this is to copy A1:C3, and then click on cell D2, use Edit >
Paste Special and then tick the box next to "transpose". This will put the
data into the correct cells for you.

Hope it helps
Katie

 

[Guest]

Thank you very much for your replies. However, I am not sure if my
explanation was very clear. I will try again.
If: B2=56; I want want a formula that uses the value of B2 as the row number
for B3. Therefore, B3=($A[value of B2]).

I have a huge number of columns from which I need to extract data at
specific points, but those positions change from column to column and sheet
to sheet. Therefore, I need a formula that automatically gives me the value
using the row I have selected....

Does this make any more sense???

Thanks!

 

[Guest]

I don't think that you can do this without using VBA, an option would be to
write your own function which you can type into the cell, for example B3.
You might like to try the following:

Function PasteData(DestinationColumn As Range, DestinationRow)
CopyColumn = DestinationColumn.Column
CopyRow = DestinationRow
PasteData = Cells(CopyRow, CopyColumn)
End Function

Then when you are in excel, in Cell B3 type the following:
=PasteData(A1,B2)

This should work

Obviously, you can rename the function to whatever suits you.

The reference to A1 only takes the column information from the cell, so any
cell in row A will work here, it just makes it possible to copy the formula,
if you need to.

Hope that this solves your problem.

Katie

Thank you very much for your replies. However, I am not sure if my
explanation was very clear. I will try again.
If: B2=56; I want want a formula that uses the value of B2 as the row number
for B3. Therefore, B3=($A[value of B2]).

I have a huge number of columns from which I need to extract data at
specific points, but those positions change from column to column and sheet
to sheet. Therefore, I need a formula that automatically gives me the value
using the row I have selected....

Does this make any more sense???

Thanks!

Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the
reference for the row value for a different series of cells (D2:F4). For
example:
Column A B C D E F
Row 1 1000 30 170 1 2 3
2 1500 60 190
3 1200 20 175
I want to get the following cells to equal:
D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1);
E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2);
F2=1200 (In other words: F2 = A3); F3=20 (In other words: F2 = B3);
...
F4=175 (In other words: F4 = C3);

Hope the explanation makes sense....I'd appreciate any help!
D. Hay