Summary Sheet. I want to reference other calculations in Sum Sheet but cannot use values
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))
Answers
-
These calculations are hopefully done separate from the Main Sheet but can be referenced in the Summary Sheet and the Main Sheet. Thanks
-
You can use this
=IF((MONTH(TODAY())) >= 10, 4, IF((MONTH(TODAY())) >= 7, 3, IF(MONTH(TODAY())) >= 4, 2, 1))
-
Spectacular!!. This variable - field is Quarter1. Can I reference Quarter1 in the Main Sheet?
Big thanks! C
-
Yes it should be able to be referenced in any of your other sheets using a cross sheet ref in a formula.
-
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
-
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?
-
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.
-
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]#
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!