Variable row number...

[Hosemunkey]

I'm using office XP, and I've got this odd sort of roadblock. I've got a workbook with two worksheets in it, one of them being the raw data arranged in a row, the other being a (hopefully) printable sheet.

What I'd LIKE to be able to do is, on the second sheet, be able to put in the row number somewhere off the printed area, and have the fields there pull from the first sheet. Basically, I want to be able to reference something from the second sheet using an absolute column name, but a variable row name...something like ='Sheet1'!A(ROWNUM). Except that doesn't work...

Thanks!

 

[larai]

Try using this formula:

=OFFSET(Sheet1!$A$1,$A2-1,0)

a) where Sheet1!$A$1 is your first row/column of data
b) $A2 is the location of the cell where you have entered the row number you wish to view data
c) 0 is the column of data you want to view, in this formula it is the same column as the cell the formula above would be in.

My spreadsheet looks like:
Sheet2
cellA1 says Row
CellB1 says Formula
CellA2 = 2 (I want to see data in row 2)
CellB2 = =OFFSET(Sheet1!$A$1,$A2-1,0) (and returns the value on Sheet1!A1)

Please post back if you have problems or email me @ larai@saturnee.com

 

[lenze]

Say on the sheet 2 you place the row number in F3 (Say 4). Then this will return the value of A4 from sheet 1

=INDIRECT("SHEET1!A"&INDIRECT("F3"))

HTH

 

[Hosemunkey]

w00t, they both worked! Thanks a ton!