Re: Formulas using the "IF" function.
I have another question.
Can I create a formula where the result is dependent upon the color of shading in the cell? In other words if the cell is red the value will come from cell c53, if the cell is red the value would come from cell c54, etc.
Re: Formulas using the "IF" function.
That appears to have solved my problem, thanks.
Re: Formulas using the "IF" function.
You are correct that the formula is in V53 and I would like it to return a value based on the code in T53. I revised the formula to this but it still is not working for me.
IF(T53="SC"(V53,E68),IF(T53="CIR"(V53,E69),IF(T53="so"(V53,E70))))
I am attempting to use the following formula but am getting a #NAME? error but I am not sure what my error is. The value cell T53 is a letter code and there are dollar values in cells E68 to E70. Any help would be appreciated.
=IF(T53=SC,V53=E68,IF(T53=CIR,V53=E69,IF(T53=so,V53=E70)))
Re: Updating cell reference when creating a new spreadsheet
That worked!
Thanks for all of your patience and help Luke.
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
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
This is the formula that is J51
=+J49-J50
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
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
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
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
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
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.
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?
Re: Conditional formatting
Thanks for all of your help and patience.
Re: Conditional formatting
forum.ozgrid.com/index.php?attachment/64854/
Apparently I had an issue with my file last week. New week and new results. I believe that I have it figured out now and it appears to be working as intended. I have attempted to attach my file for you to look over but am not sure if I have successfully attached it. Thanks for all of your help.
Re: Conditional formatting
I appear to have the same formulas and conditional formatting as you but continue to get the same results. I have also been trying to attach a copy of my most recent spreadsheet but am unable to do that as well.
Re: Conditional formatting
My formulas and conditional formatting work as intended and highlight the cell with the lowest value when there is a value returned in 2 or more of the cells H35, L35, P35 & R35. However, occasionally there is a value in only one of the cells and in the case the 3 cells with no value in are all highlighted. All 4 cells have the same formulas and conditional formatting.
Thanks for your assistance
Re: Conditional formatting
I hope that I have successfully attached the file.
