Sign in to join the conversation:
Is it possible to conditionally format a cell where based on the answer to one cell the formatted cell changes its formula?
I am not sure I follow the question. (Sorry)
You can conditionally format a cell based on a different cells value. Is that what you mean? With conditional formatting you wouldn't be changes any cells formula... it is just a colouring / highlighting tool.
Apologies if I have missed the point.
Kind regards
Debbie
You could write an IF statement that looks at the status of a cell and provides a particular formula if the cell has a certain status. In my screenshot below I am changing the output of the cell based on whether the checkbox is checked or not. If it is checked I add number 1 and number 2 together, if not I sum the entire row of number 1.
We are tracking downtime and cost associated. Each machine center has a different cost associated with it. So based off the machine center that is chosen I wanted a different formula to be input to calculate cost of that downtime. Doesn't seem like that is doable though.
I've been working on some of these concepts... lately I've just been tossing a "style" column at the far right which looks at other values and such to generate a number. Then you just specify a conditional format when the style value is 3, 13, 23 or 33 (or whatever your conditions are).
Here's a timesheet test I did a while ago:
https://community.smartsheet.com/discussion/time-formats-number-formats-rethinking-ux-and-function
Hi Mitch
Sounds to me like you could do with a lookup table. Set up a sheet (or an area in a sheet) which lists the machine types and the associated costs; then in the sheet where you are selected which machine center has been used, the cost column next to it would do a vlookup function based on matching the machine center entered in that record with the machine center lookup table and return its associated cost.
Here is an example screen shot... (The vlookup can go in a different sheet, I just kept it on the same sheet for ease of reference!)
Hope this helps.
(Sorry Mitch having trouble adding the screen shot - hope this works!)
Did any of these solutions work out for you?
I think the vlookup is the best option for my use case. I haven't had all the time I'd like to work on this project but today my goal is to get this part figured out. I will post results end of the day with what worked best for my use.
Thank you all
The Vlookup worked perfectly, thank you for all the help
I am pleased you found a solution.
I am trying to highlight a Date cell (Column1) if it is greater than a date in a different cell (Column2).
are you able to use variables in this way within conditional formatting?
The only way I've been able to figure out how to do this @R.McCallie is to have a separate column (can be hidden) that does the calculation and sets a flag. The cell to be formatted can then just check the flag. For example, if your case...
In new column 'Style':
=IF(Column1>Column2,1,0)
Then set formatting in Column1 to check for 'Style' is equal to 1.
This is a simple case of what @Ezra describes above.
Hello, I'm working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
I have an automation that has a condition where I am looking at the column "LEA Interco Supporting Site(s)" and trying to find columns that have the value "Fort Loramie" in them. This works well when "Fort Loramie" is the first item listed in the cell, but it doesn't work when "Fort Loramie" is the second or third value in…
Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026. I have done it on Excel sheets manually, but I think there should be a way to do it with…
