Summary Sheet. I want to reference other calculations in Sum Sheet but cannot use values

Doyle54

I want to calculate the quarter of the year it is today()... Below I calculated Month1(Today()). Below comes after and I want to determine the Quarter. Can I use the Month1 value calculated previously? Right now the equation below is "Unparsable". Thank you. C

=IF(Month1<4, 1, IF(AND(MONTH1 < 7, MONTH1 > 3), 2, IF(AND(MONTH1 < 10, MONTH1> 6), 3, 4))

Doyle54

These calculations are hopefully done separate from the Main Sheet but can be referenced in the Summary Sheet and the Main Sheet. Thanks

RossL

@Doyle54

You can use this

=IF((MONTH(TODAY())) >= 10, 4, IF((MONTH(TODAY())) >= 7, 3, IF(MONTH(TODAY())) >= 4, 2, 1))

Doyle54

Spectacular!!. This variable - field is Quarter1. Can I reference Quarter1 in the Main Sheet?

Big thanks! C

RossL

@Doyle54

Yes it should be able to be referenced in any of your other sheets using a cross sheet ref in a formula.

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Doyle54

Since the Summary Sheet is an extension of the Main Sheet, I should be able to simply reference it in the Main Sheet by its Variable Name.

Mr Smart Person - Can I ask you to help an old man to figure out the coding of a concept? I work on an Indian Reservation and I'm helping the energy department with the Administrative Field Management Needs. We have 625 Well Pads that they need to inspect quarterly. I have a Sheet for the Well Pads (WP) and their locations and I have a Pad Inspection (PI) Sheet.

The work concept starts with the automatically checking the "Visited Pad" field in PI if the date on their visit changes. The change signals that the Field Officer is at the Pad. And then I can use Index in the WP for auto update the pads visited column field using the Quarter calculation in Sum Sheet. Again, there would be a field - column in WP - PV - that the Pad had been visited. 1 How do I automatically reset the PV as the quarter changes? 2 Is there a way to toggle Hide and Unhide of a record in the WP sheet based on an input into a cell?

Thanks.

Carey

RossL

With Smartsheet formulas you are not able to call variables like you can in coding. If you are referencing a sheet summary field back in the main sheet you can just select the sheet summary field in your formula. it will look something like this [Quarter 1]#

Do you mind sharing a screen shot of your two sheets?

Doyle54

Rossi QuarterYr Summary Sheet Variable.jpg

RossL - I apologize. I did not see your request. You can see from the image that the [QuarterYr]# is not working.

Also, I want to shift the Pad Inspection...Range to Pad Inspection [Loc...]:[Loc...] Format because setting it up as a Range does not work as Field Officers add more and more Rows.

Doyle54

Rossi QuarterYr Summary Sheet Variable.jpg

Closer

=IF(AND(Combine Loc & Name1={Pad Inspection Report Sheet 7-7 2 [CombPN&L]:[CombPN&L]}, [QuarterYr]#={Pad Inspection Report Sheet 7-7 2 [QTRfiscal]:[QTRfiscal]}, 1,0)

Or Should it be:

=If(AND(MATCH($[Combine Loc & Name]@row, {Pad Inspection Report Sheet 7-7 2 Range 1})=TRUE, (Match([QuarterYr]#, {Pad Inspection Report Sheet 7-7 2 Range 2})=TRUE,1,0)

Unfortunately, I think SS is rejecting the [QuarterYr]# as SS keeps taking out the parenthesis for [QuarterYr]#