+ Reply to Thread
Results 1 to 6 of 6

a 365 day report..easy way to create formula that repeats adding the previous sheet?

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    a 365 day report..easy way to create formula that repeats adding the previous sheet?

    I am doing a daily report and I am inputting all the formulas in but is there an easier way to have it automatically add the previous day without having to type in the previos sheet number each and every time...
    =(Sheet185!$F$5)+B5 then the next day =(Sheet186!$F$5)+B5...then next day =(Sheet187!$F$5)+B5...i have so many formulas that it takes forever to just go back through and change one number each day..I have already copied all the formulas over up until the end of this year but now I have to go back and change each sheet number on each formula..ugh..i just want a way for it automatically puts the previous sheet number in..

    I'm sorry if this is a stupid question as I am pretty new to this..help please
    Thanks in advance!
    [SOLVED]
    Last edited by acolyte13; 08-18-2012 at 08:28 PM. Reason: solved

  2. #2
    Registered User
    Join Date
    08-18-2012
    Location
    florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    Untitledexcel.jpg

    this is what i am doing...i just haven't went through everything to change to previous sheet..

  3. #3
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    Hi,

    assuming you want to change Sheet 185 to Sheet 186

    Select the column/cell where you need to change the sheet and press Ctrl+H. Type Sheet185 in Find What: field and Sheet186 in Replace With Field and Press Replace All

    Once you've got this typed once, then you will just need to change the last digit of each sheet number

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    try this as a suggestion. you will need to adjust the references to suite...

    =INDIRECT("Sheet"&ROW(D2)&"!"&"$A$2")+C12

    see the attached
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    08-18-2012
    Location
    florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    Quote Originally Posted by wakeupcall View Post
    Hi,

    assuming you want to change Sheet 185 to Sheet 186

    Select the column/cell where you need to change the sheet and press Ctrl+H. Type Sheet185 in Find What: field and Sheet186 in Replace With Field and Press Replace All

    Once you've got this typed once, then you will just need to change the last digit of each sheet number
    Thank you soooo much!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    if your data is in column format, the formula i gave you should make those changes for you automatically

  7. #7
    Registered User
    Join Date
    08-18-2012
    Location
    florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: a 365 day report..easy way to create formula that repeats adding the previous sheet?

    yep..in column form..oh i see what you did..that is even easier..thanks so much!

+ 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