You are here:

Excel/Dragging a formula down but letting the the sheet refrence change each time


budsimrin wrote at 2012-04-18 01:36:18

Thank you so much for your answer. It took me a while to digest so I thought I might share what I learned. Perhaps you released this information in an up-front part of this thread that I missed. If so, please excuse me for taking up time.

I was not previously familiar with the Indirect function of Excel. I learned that if cell A1 has entry F5, and I enter =A1 in, say, cell A2, then the answer in cell A2 is (of course) F5. But if I enter Indirect(F5) it returns whatever is in cell F5. That is the basics.

I had a need to grab information from, a cell, say G10, in another tab. I found out that if the other tab has simple name without spaces, such as Sheet2,and if I have the name Sheet2 stored as data in my current tab,in, say, cell A1,  then I can write a not very complicated formula like =Indirect(A1&"!G10"). I thus learned that & is used for concatenating the parts of the full name for cell G10 which is Sheet2!10. I learned that all parts of the cell name should be in double quotes.

I struggled but finally learned that if the other tab has a more complicated name such as Sheet 2 (with a space), the I must enclose the name in single quotes. That is, I would have to enclose the single quotes inside of double quotes like =Indirect("'"&A1"'!G10). When I read your example, I couldn't for the life of me at first figure out that when you had written "'" it was a single quote inside of a double quote. I also didn't know why such a thing was needed. Thank you for pointing me in the right direction.

The real kicker is that I actually had a problem one step more complicated. In Tab Sheet 1 I needed to get information from cell D8 in another tab, say named Sheet 2, but the name Sheet 2 was stored in cell A1 of a 3rd tab, Sheet 3. To do this, I not only needed to use all of the tricks you showed (use of Indirect, use of & to concatenate, use of single quotes around the other sheet names, and use of double quotes as the argument for the Indirect function), I actually needed to use nested Indirect functions. The inside Indirect function gets the tab name Sheet 2 from cell A1 of Sheet 3, and the outer Indirect function to append the single quotes, exclamation point, and cell reference to the name Sheet 2. My command was =Indirect(Indirect("'Sheet 3'!A1")&"!D8").  

SJ3312 wrote at 2012-12-31 18:12:35
I am attempting something similar.This formula =Sheet1!$G98 to drag down in the column but only change the sheet number.

Is this possible?

Thanks - SJ

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.