Automatically increment sheet reference when dragging formula

excel excel-formula worksheet-function excel-indirect
OneFineDay picture OneFineDay · Oct 28, 2014 · Viewed 18.2k times · Source

I have a formula with the following syntax: 

=SheetName!E10

and need some way to drag the formula and change just the sheet name as it moves to the right. So the E10 part needs to stay the same with the sheet number incrementing as I drag the formula. We have many rows and columns to do and this would definitely speed us up.

Is this possible?

So the result would look like this:

=Sheet1!E10    =Sheet2!E10    =Sheet3!E10

This is the result:

SO26599056 question example

from applying this formula:

=INDIRECT("mo"&COLUMN()+0&"!B4")

Answer

pnuts picture pnuts · Oct 28, 2014

Please try: 

=INDIRECT("Sheet"&COLUMN()+x&"!E10")

where x is the offset to return the appropriate number ( Column()+x ) for wherever you choose to place the formula.

Edit to match subsequent details from the image (where 5 may be in ColumnB) and a comment, perhaps should be: 

=INDIRECT("mo"&COLUMN()-1&"!E10")

Related questions

Returning cell values from other sheets by referencing sheet names from a list

I have a spreadsheet with approx twenty different sheets named by stock ticker. Because these are templates, the information in each sheet is found in the same cells. For example, the EPS for the current year is always in cell …

Read More
Adding previous cell and current cell functions

I like to have an Excel Function for: Previous cell + Current Cell = Current cell to do this function for the entire row, when I enter a new value each time, Eg. A1+A2= A2.

Read More
INDIRECT with column reference and row reference in different cells

So I have two cells, both referring to another sheet. One cell has the row of the other sheet and another has the column. I tried using an indirect statement along the lines of this: =INDIRECT("'VENDOR COSTING'!R"&…

Read More