Can't get my formula to work.

JimmG07

The Formula below works in excel, but because ROW isn't a formula in Smartsheet, I can't figure out how to get it to work. Can anyone help me out here?

=IF(ROW()=1,B$1,(IF(B4="May Require Attention",1,(IF(B4="Requires Immediate Attention",2,"")))))

JimmG07

I was able to make a formula that works by creating another helper column, but now because I have specific cell references and absolute references I can't make if a formula column. the working formula is below

=IF($[ROW NUM Helper]@row = 1, [Vehicle Oil Level]$1, IF([Vehicle Oil Level]@row = "May Require Attention", 1, IF([Vehicle Oil Level]@row = "Requires Immediate Attention", 2, " ")))

any help to make this work would be appreicated

Kelly Moore

Hey @JimmG07

You will need to remove the absolute reference to your [Row Num Helper]@row. To mitigate your absolute cell reference, create a Summary field that references your Vehicle Oil Level. It's formula would be =[Vehicle Oil Level]$1.(Summary fields are in the right ribbon on your sheet)

Use the Summary Field in your formula. Let's say you named it [Level of Vehicle Oil]. Your formula would then become

=IF([ROW NUM Helper]@row = 1, [Level of Vehicle Oil]#, IF([Vehicle Oil Level]@row = "May Require Attention", 1, IF([Vehicle Oil Level]@row = "Requires Immediate Attention", 2)))

This formula can become a column formula. Will this work for you?

Kelly

JimmG07

The issue with that is I need to duplicate this formula 62 times, so without the cell reference, and absolute reference, duplicating it 62 times means creating 62 summary fields and physically typing the correct column and summary field reference 62 times so that it works properly... I can see it is a functional work around, but seems ridiculous that a working formula can't be utilized as a column formula

Kelly Moore

Hey @JimmG07

Ouch, summary fields definitely won't work.

Is it possible to share a screenshot of your sheet - even an excel mock up (no sensitive data) to better understand your needs? It is possible there are other functions that would work better in smartsheet rather than trying to replicate what you had to do in excel.

Kelly