create a formula that when I type a number in that cell it will to

  • Thread starter Thread starter highland
  • Start date Start date
H

highland

I am trying to create a formula that when I type a number in that cell it
will to another page and go to the column #(which is the number Ive typed in)
and bring back the value and place it in another cell.


Any help would be appreciated.
 
If you only give it the column #, it does not know which row in the column to
select. Let us then give the formula two values, the column letter and the
row number.

In A1 enter:

AA

In A2 enter:

100

=INDIRECT("Sheet2!" & A1 & A2)
This formula will go to Sheet2, then go to column AA, then go to row 100 and
retreive the value.
 
Hi Highland:

You could try using offset on the cell where you want the answer.

So if the data entry is in cell a1 on sheet sheet1 and the answer is in a2 put

=OFFSET(Sheet2!A1,0,Sheet1!A1-1).

Remeber that the offset uses first cell as 1,1 so here you need to deduct one.

This works if the sheet is in the current workbook.

If it is not then try the index function:
=INDEX('C:\Documents and Settings\Dad\My
Documents\[Book2.xls]Sheet2'!$A$1:$M$1,0,Sheet1!A1)

This works on closed books.
 
Highland --

Here's something that worked for me. My workbook had a worksheet named
'Location', so substitute the name of your source worksheet to fit:

=INDIRECT("'location'!" &"R" & 1 &"C" & D1,FALSE)

where Location is the name of the worksheet
1 is the row where the data will be
D1 is the where you'll type the column number you want to extract.

HTH
 
Back
Top