Using Column Name in Formula

Options

Hi everyone. I am really struggling which I am sure is something stupid on my part. I have a sheet named "Summary" and am using the below formula thats adding information from another sheet. I want to replace the "Range 1" in the formula with the name of the column "Date PIPS Checked". I have tried several things but keep getting INVALID REF errors. Can you please help?

This is the formula I am struggling with

=COUNTIFS({CY24 Intake Station - Personnel Securit... Range 1}, <>"", {CY24 Intake Station - Personnel Securit... Range 1}, >=[Begin Date]1, {CY24 Intake Station - Personnel Securit... Range 1}, <=[End Date]1)

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    Hi,

    For clarification, is the "Date PIPS Checked" on the other sheet or your current one?

    • The {} brackets indicate that you are pulling that range from another sheet. If that is the case and you are using {CY24 Intake Station - Personnel Securit... Range 1} actively on your current sheet, you would want to create a new inter-sheet reference of the "Date PIPS Checked" column. You could then name that range anything you like. If you are not using the "Range 1" above, you could simply choose to edit the range, select the column, and rename it.
    • If the "Date PIPS Checked" column is within your same sheet you should be able to simply replace it. It would look like: =COUNTIFS([Date PIPS Checked], <>"", [Date PIPS Checked], >=[Begin Date]1, [Date PIPS Checked], <=[End Date]1)

    Please note that this assumes the data in that column is compatible with the calculations you are attempting to perform.

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Peppey
    Peppey ✭✭
    Options

    Hi Zach. Thank you so much for your help. The column "Date PIPS Checked" is on a different sheet than the one I am working in. The sheet I am working in with the formula is titled "Summary" and the column titled "Date PIPS Checked" is in the worksheet titled "CY24 Intake Station - Personnel Securit... Range 1", I am sorry I do not understand about the inter-sheet reference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!