Excel offset where reference is to another worksheet

0 votes
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.
Oct 7, 2022 in Others by Kithuzzz
• 38,010 points
1,751 views

1 answer to this question.

0 votes

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

answered Oct 8, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Is there a way to produce a sum according to date/time stamp values of another column on excel?

Assuming the input data for the second ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,420 points
384 views
0 votes
1 answer

How to change where an email is sent FROM in Excel VBA

Inside the 'with out mail, insert this ...READ MORE

answered Mar 30, 2023 in Others by narikkadan
• 63,420 points
764 views
0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,210 points
510 views
–1 vote
0 answers
0 votes
1 answer

Excel OFFSET function with a dynamic row argument

The answer is to make "anchor cells" ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,420 points
623 views
0 votes
1 answer

VBA Range.Offset Error 1004 Range beyond scope of sheet Dynamic Range

Check the .Row first: With Sheets("Line 3").Range("G1024").End(xlUp) ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
512 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
721 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
875 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
1,001 views
0 votes
1 answer

Excel VBA - Using Error Traps where Another Routine is Called

Macro1 is illegible as syntax. After the ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,420 points
182 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP