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

Options

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

  • Doyle54
    Doyle54 ✭✭✭✭✭
    Options

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

  • RossL
    RossL ✭✭✭✭✭✭
    Options

    @Doyle54

    You can use this

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

  • Doyle54
    Doyle54 ✭✭✭✭✭
    Options

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

    Big thanks! C

  • RossL
    RossL ✭✭✭✭✭✭
    Options

    @Doyle54

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


  • Doyle54
    Doyle54 ✭✭✭✭✭
    Options

    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
    RossL ✭✭✭✭✭✭
    Options

    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
    Doyle54 ✭✭✭✭✭
    Options

    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
    Doyle54 ✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!