Variable row number...

Hosemunkey

New Member
Joined
Aug 28, 2002
Messages
2
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

larai

New Member
Joined
Aug 28, 2002
Messages
20
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,148,525
Messages
5,747,188
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top