# Variable row number...

#### Hosemunkey

##### New Member
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.

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

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

w00t, they both worked! Thanks a ton!

Replies
2
Views
209
Replies
4
Views
519
Replies
0
Views
752
Replies
6
Views
591
Replies
4
Views
567

1,219,877
Messages
6,150,714
Members
450,982
Latest member
L2LExcel

### 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.

### Which adblocker are you using?

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

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