dragging an equation but only changing one part of the equation

**mufan** · 04-09-2012, 12:11 PM

Hello,
I have the following equation that I want to drag from cell 1 of a worksheet to cell 30.

=+'D:\Documents and Settings\218019778\Desktop\Accounts\Duxon (Melle)\Waste Treatment\Operator Log Sheet\[Operator Log Sheet Example.xlsx]Day 2'!$B$19

I want to keep the equation exactly the same in each cell I drag it to except that I want the Day (marked in red in the equation) to sequentially increase. For instance, I want the day to change to day 3, day 4, day 5, and so on as I drag the equation down.

Is this possible?

thanks!

**DGagnon** · 04-09-2012, 01:21 PM

You can do this, but you will need to use indirect, which would also prevent the link to a closed workbook. i think your best bet would be to manualy change the date. if you have more that 10 or so, you could create a macro to automaticaly change the date for you.

**Richard Buttrey** · 04-09-2012, 01:27 PM

Hi,

="'D:\Documents and Settings\218019778\Desktop\Accounts\Duxon (Melle)\Waste Treatment\Operator Log Sheet\[Operator Log Sheet Example.xlsx]Day "&ROW()-6&"'!$B$19"

Change the -6 bit to be the integer of the row in which you put the formula -2. e.g. If in Row 8 as my example it's -6, if in Row 10 it's -8 etc.

I've assumed by Day 2 you do literally mean the characters 'Day 2' and not a reference to an actual date.

**icestationzbra** · 04-09-2012, 01:29 PM

duplicate question:

http://www.excelforum.com/excel-gene...-equation.html

**mufan** · 04-09-2012, 03:20 PM

Hi,
Thanks for the responses. I'm still a bit confused. I have attached an example of the two sheets that I am talking about. Essentially, I am pulling data from the same cells on different tabs of "operator copy sheet" and inserting them into the "sheet that I want to add data to".

So day 1, day 2, and so on are the names of the tabs. These tabs correspond to the days of the month. In the "sheet that i want to add data to" I want to be able to drag the reference down and have it go from day 2 to 28 but keep the same cell reference.

**Richard Buttrey** · 04-09-2012, 03:52 PM

Hi,

=INDIRECT("'[Operator copy sheet.xls]Day "&ROW()-6&"'!$B$9")

Please note that the Day 1 sheet has a trailing space in the tab name that doesn't exist in the other tabs. Hence this formula will fail on the first row unless you correct the tab name.

**mufan** · 04-12-2012, 11:25 AM

Richard, This works great. I have no idea what it is doing or how it is doing it but it works perfect. Many thanks!

Can you explain in layman's terms what the equation is telling it to do?

Thanks!

**Richard Buttrey** · 04-12-2012, 02:48 PM

Hi,

The INDIRECT() function is a way of using text and numbers held in other cells within a formula. Your current formula +'C:\Users\Richard\Downloads\[Operator copy sheet.xls]Day 1 '!$B$9, which links AR7 to the Operator copy sheet has within it the Reference to 'Day 1'. As you can see just copying that to another cell doesn't change anything. The task is to arrange the formula so that the '1' bit of the formula changes as you drag it down.

To do that we use the =ROW() function. This returns the row number of the cell which contains the formula. In your example the =ROW() since it's part of a formula on row 7 returns the value 7. We want it to return the value 1 so we just deduct 6 from it. If you think about it as you drag that formula down the =ROW() number increases but the 6 is a constant so on the next row 8, =ROW()-6 returns the value 2.

So now we have a way of getting the 1,2,3..etc of the sheet names. Fortunately all the sheet names start with the same text 'DAY ' so all we need to do is put all these elements together and we need INDIRECT() to do this.

So inside the INDIRECT() function the first bit is straightforward text where we refer to the workbook and worksheet name so we include them in text literal quotes i.e. "'[Operator copy sheet (2).xls]Day "
Now we need to append the day number so we add the row number with '&Row()-6'
Finally we need to complete the name of the workbook and worksheet defining characters, and the cell you want to get the data from. Which because it's always B9 we can include in the text literal quotes. So we add &" '!$B$9".

Hope that helps. INDIRECT is not particularly intuitive so it's often hard to puzzle your way through it at first.