+ Reply to Thread
Results 1 to 8 of 8

dragging an equation but only changing one part of the equation

  1. #1
    Forum Contributor
    Join Date
    03-07-2008
    Posts
    140

    dragging an equation but only changing one part of the equation

    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!

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: dragging an equation but only changing one part of the equation

    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.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,410

    Re: dragging an equation but only changing one part of the equation

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: dragging an equation but only changing one part of the equation


  5. #5
    Forum Contributor
    Join Date
    03-07-2008
    Posts
    140

    Re: dragging an equation but only changing one part of the equation

    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.
    Attached Files Attached Files

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,410

    Re: dragging an equation but only changing one part of the equation

    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.

  7. #7
    Forum Contributor
    Join Date
    03-07-2008
    Posts
    140

    Re: dragging an equation but only changing one part of the equation

    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!
    Last edited by mufan; 04-12-2012 at 11:35 AM. Reason: found answer to question I asked

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,410

    Re: dragging an equation but only changing one part of the equation

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1