Excel offset where reference is to another worksheet

Question

I'm attempting to use a number of Excel formulas to display data from several rows on sheet 2 by referencing a column on sheet 2 through a dependent cell on sheet 1. I've looked here for some advice on this issue and found that utilizing Offset is probably the solution, but I still need some help with the last component.

Basically, I need to be able to write the formula "=Sheet2!A1" into cell A1 on Sheet1 and have the contents of other specified cells on row 1 in Sheet2 immediately appear in cells A2, A3, A4, etc. on Sheet1 via a =offset formula.

This is because sheet 2 is a sizable database, and I'm attempting to build up sheet 1 as a summary sheet where users can display specific data.

If all the data were on one sheet, I can see how I could use the =Offset function in cell A2 on Sheet 1 and how I could set the reference in the Offset formulas to =sheet2!A1, but I actually want the reference to be A1 and the offset to follow the target of A1 into sheet 2 and then locate the data there. Can anybody assist? Even so, is this possible? Am I approaching this correctly? Because the spreadsheet will be utilized by persons whose computers cannot run macros, I am unable to use macros.

Answer 1

=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])