Excel offset where reference is to another worksheet

Question

I am trying to create a series of formulas in Excel that use a reference on sheet 2, via a dependent cell on sheet 1 to display data from different rows on the sheet 2. I have searched here for some tips on this problem and discovered that using Offset is probably the solution, but I need some help on the last piece of the puzzle.

Basically, I need to be able to enter into A1 on Sheet1 the formula "=Sheet2!A1" and then, via an =offset formula have cell A2, A3, A4 etc on sheet 1 automatically display the contents of specified cells elsewhere on row 1 in Sheet2.

The reason for this is because sheet 2 is a large database and i'm trying to set-up sheet 1 as a summary sheet where users can display select information.

I can see how I could use the =Offset function in cell A2 on Sheet 1 to make this work if all the data was on one sheet, and how I could set the reference within the Offset formulas to =sheet2!A1, but what I want is for the reference to be A1 and for the offset to follow the target of A1 into sheet 2 and then locate the data there.

Can anyone help? Is this even possible? Am I going about this the right way? I can't use macros because the spreadsheet will be used by people who cannot have macros on their PCs

Thanks

It's hard to understand what you exactly need. Is the location of what you want displayed A2, A3, and A4 on Sheet 1 consistent in the data sources' locations on Sheet 2? Or are you trying to find cells with certain contents and then populate A2 through A4 with that? — Dan
– Dan, Commented Feb 25, 2016 at 18:25
Hi Dan - it is the latter. Sheet 2 is a large database and Sheet 1 is a summary sheet. I want the summary sheet cells 1, 2, 3, 4 to show cells from columns 2, 5, 6, 10 in the database sheet. The summary sheet doesn't use all the rows either. The requirement comes about when a new row is entered into the database sheet and I want to add the relevant data into the summary sheet. I'd like to be able to set the first column in the summary sheet to the relevant cell in the database sheet, and then have the other cells automatically populate with the right data. Any suggestions appreciated! Thanks — RSmith36
– RSmith36, Commented Feb 26, 2016 at 14:58

Azhar Khan · Accepted Answer · 2022-12-31 13:35:51Z

I had this same question I'll share my solution.

Solution:

=OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),ROW(),0,,)

References:

OFFSET(starting point, num of rows, num of col, [height], [width])
ADDRESS(row_num, col_num, [abs num],[a1],[sheet])

OFFSET can't take a reference from another sheet by itself. Neither will ADDRESS for some reason. In order to reference another sheet within a formula, you need to nest ADDRESS within INDIRECT like so:

INDIRECT(ADDRESS(1,1,,,"Sheet2"))

These two nested functions point to your Sheet1 cell A1. The first argument in OFFSET is your starting point, so you need to take the two nested functions and insert as the first argument to OFFSET like so:

=OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),1,0,,)

Now you need to get that "1" to increment. The best way to do this is the ROW() function, which can return the row number of the current cell (for you, this is Sheet1 A1, returning a 1). Now we have:

=OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),ROW(),0,,)

If you had started your formula in Sheet1 A5, instead of ROW(), you would need to insert (ROW()-4). Row 5 minus 4 equals 1. That's the part that I needed.

Hopefully this helps someone.

Collectives™ on Stack Overflow

Excel offset where reference is to another worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related