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 in Others by Kithuzzz
• 20,660 points
122 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 by narikkadan
• 37,660 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
321 views
–1 vote
0 answers
0 votes
1 answer
0 votes
1 answer

Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
64 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 in Others by narikkadan
• 37,660 points
86 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,720 points
454 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
405 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,384 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 in Others by narikkadan
• 37,660 points
43 views
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 in Others by narikkadan
• 37,660 points
77 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