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
• 28,700 points
317 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
• 53,160 points

Related Questions In Others

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,190 points
339 views
–1 vote
0 answers
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 53,160 points
151 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,160 points
1,153 views
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
• 53,160 points
175 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 in Others by narikkadan
• 53,160 points
47 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 in Others by narikkadan
• 53,160 points
45 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
530 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
• 53,160 points
156 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 in Others by narikkadan
• 53,160 points
33 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