When I am copying and updating a spreadsheet within a workbook I want a specific cell in the newest worksheet to default to and always refer to a specific cell in the previous version. Is this possible and how is it accomplished?
Updating cell reference when creating a new spreadsheet
-
-
-
Re: Updating cell reference when creating a new spreadsheet
What do you mean by "previous version"? How are the sheets named?
-
Re: Updating cell reference when creating a new spreadsheet
What I am doing is continually updating a worksheet within a workbook by making a copy and updating the data as necessary. The newest worksheet is an updated version of the previous and I would like to update the value from an individual cell on the previous version with additional data. Since I am continually creating a new updated worksheet with a reference to the same cell I would to be able to link the new sheet to the previous without having to re-enter the formula every time I create a new spreadsheet.
-
Re: Updating cell reference when creating a new spreadsheet
Since I don't know how your sheets are named, I can't help atm.
The only hope is that they named after dates, and we could try to build an INDIRECT function to find the previous sheet name.
A better workbook design would be my personal suggestion. Having all the data in a single worksheet lets XL function much better, and perform better data analysis. -
Re: Updating cell reference when creating a new spreadsheet
They are actually Pay Estimates named Est.#1, Est.#2 etc. When I create the next estimate I refer to the previous est. for the amount paid and that quantity is deducted off the amount due on the newest Est. It would be nice if when I created the newest version that a formula would refer to the amount paid on the previous version without me having to update the formula every ti,e that I ceated a new Estimat.
I will be out of the office until next week but will follow up on any correspondence on Monday -
-
Re: Updating cell reference when creating a new spreadsheet
Thank you, the sheet names was what we needed. Formula to look at A2 of previous sheet:
=INDIRECT("'Est.#" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99)-1 & "'!A2")
Change the A2 at end of formula to be whatever cell(s) you need.
-
Re: Updating cell reference when creating a new spreadsheet
Here is a copy of my formula but apparently I didn't do something right because I get the #VALUE! error message.
=INDIRECT("'Est.#1"&MID(CELL("Doc's Excavating",A1),FIND("]",CELL("Doc's Excavating",A1))+6,99)-1&"'!J51")
-
Re: Updating cell reference when creating a new spreadsheet
Don't replace the word "filename". It the argument that tells the CELL function what to return.
=INDIRECT("'Est.#1"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99)-1&"'!J51") -
Re: Updating cell reference when creating a new spreadsheet
Here is a copy of my formula but I am still getting the "#value"error message
=INDIRECT("'Est.#1"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99)-1&"'!J51")
-
Re: Updating cell reference when creating a new spreadsheet
Oops, I missed that you had hard-typed the 1 at beginning.
=INDIRECT("'Est.#" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99)-1 & "'!J51") -
-
Re: Updating cell reference when creating a new spreadsheet
Here is a copy of my formula but I still get the same results.
=INDIRECT("'Est.#"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99)-1&"'!J51")
-
Re: Updating cell reference when creating a new spreadsheet
Not sure what else it could be. Perhaps seeing the file will help you?
-
Re: Updating cell reference when creating a new spreadsheet
Your formula looks just like mine and you are accomplishing exactly what I am trying to accomplish. I have a formula in the J51 cell that the determines the value of the data in that cell, could that be the source of the problem?
-
Re: Updating cell reference when creating a new spreadsheet
This is the formula that is J51
=+J49-J50
-
Re: Updating cell reference when creating a new spreadsheet
Nope. Just as long as the sheet Est.#1 doesn't have the INDIRECT formula, should be ok.
What is your formula outputting? -
-
Re: Updating cell reference when creating a new spreadsheet
I am attempting to attach a copy of my spreadsheet but am not sure if it was successful. The formula returns a dollar value[ATTACH=CONFIG]68757[/ATTACH][ATTACH=CONFIG]68757[/ATTACH]
-
Re: Updating cell reference when creating a new spreadsheet
You changed your sheet names. The sheets in the attached are labelled Est (1), Est (2). Formula needs to know exact sheet name. New formula to work with those names:
=INDIRECT("'Est ("&SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99),")","")-1&")'!J51") -
Re: Updating cell reference when creating a new spreadsheet
Here is the formula that I entered and now it returns a #REF error message
=INDIRECT("'Est("&SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99),")","")-1&")'!J51") -
Re: Updating cell reference when creating a new spreadsheet
Now you took the space out near the beginning, between the "Est" and the "(". In future, try just copy/pasting the formula given.
Wrong formula
=INDIRECT("'Est("&SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99),")","")-1&")'!J51")Correct formula
=INDIRECT("'Est ("&SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,99),")","")-1&")'!J51") -
Re: Updating cell reference when creating a new spreadsheet
That worked!
Thanks for all of your patience and help Luke. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!