Referenced Sheet Column Changes

Michelle Maas
Michelle Maas ✭✭✭✭
in Formulas and Functions

My below formula references the Finish date column, {Sales Pipeline Range 5}, and works perfectly. (This column feeds off the Duration column from the date in the Start date column).

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 5}, AND(MONTH(@cell) = 10, YEAR(@cell) = 2021)), 0)

However, if I change the Finish column to another date column, {Sales Pipeline Range 4}, it now doesn't work. (The dates are typed into this new column or selected from the calendar function in that column.

=IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 12, YEAR(@cell) = 2021)), 0)

I have done a test row with all columns blank except the four ranges referenced, and then it works. I also tried deleting all the sheet references and starting again, but it does not rectify the Range 4 issue. How can I get this formula to work?

· Share on FacebookShare on Twitter

Best Answers

  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Answer ✓

    Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle Maas

    It looks like these formulas don't have the additional IFERRORs around the MONTH and YEAR functions:

    =IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)), 0)


    Did that make a difference? If not, what error are you receiving when you take away the IFERROR from the whole thing:

    =SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

  • Michelle Maas
    Michelle Maas ✭✭✭✭

    Trying to troubleshoot the reference formula, I noticed that if I switch the value column from Weighted (formula for quote amount less percentage), to a Value column that is simply a numeric entry (no formula), then Range 4 works. Somehow when I change Range 5 to 4, it no longer picks up the Weighted amount.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Michelle Maas

    When you say it doesn't work, are you receiving an error? (What happens if you take the IFERROR away, to see what error it returns?)

    With the MONTH and YEAR functions, I generally wrap an individual IFERROR around each of these in case any of the cells in the referenced column contain text or are blank, which would cause them to error.

    Try this:

    =IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 3}, OR(@cell = "Schedule", @cell = "In Progress", @cell = "Not Started"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell) = 2021), 0))), 0)


    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle Maas
    Michelle Maas ✭✭✭✭
    Answer ✓

    Hi @Genevieve P. thanks for getting back to me. I changed the status column to another status column and it is now working.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    I'm glad it's working for you now!

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle Maas
    Michelle Maas ✭✭✭✭

    Hi @Genevieve P.

    I am having issues with this formula / sheet again. Some rows are referencing columns in another sheet for {weighted value} (% calculation), {account name}, {status}, {expected date} and the corresponding row figures are calculating fine.

    =IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 2, YEAR(@cell) = 2022)), 0)

    Yet, other rows will not calculate unless I change the {weighted value} to {forecasted value}. The rows are all together in one section on the main sheet and the formulas are reporting from the same external sheet, but I cannot understand why it works for some {account name} rows and not others.

    =IFERROR(SUMIFS({Sales Pipeline Range 1}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(MONTH(@cell) = 2, YEAR(@cell) = 2022)), 0)

    I need all the formulas to be referencing weighted and expected day. How do I fix this?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Michelle Maas

    It looks like these formulas don't have the additional IFERRORs around the MONTH and YEAR functions:

    =IFERROR(SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)), 0)


    Did that make a difference? If not, what error are you receiving when you take away the IFERROR from the whole thing:

    =SUMIFS({Sales Pipeline Range 3}, {Sales Pipeline Range 2}, [Account Name]@row, {Sales Pipeline Range 6}, OR(@cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Range 4}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Michelle Maas
    Michelle Maas ✭✭✭✭
    edited 02/03/22

    Hi @Genevieve P. , thanks for getting back to me. Yes, removing the first IFERROR solved the problem. Thank you so much!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Michelle Maas

    That helps, thank you!

    The argument set is correct if you used the same syntax as above - I've just tested on my sheet and it works fine. However, is it possible that one of your referenced columns contains an error?

    For example, if {Sales Pipeline Range 3} has an error somewhere in one of its cells, then this formula referencing it will also display the same error. Works a bit like dominos!

    Are you using a formula in {Sales Pipeline Range 3} on the source sheet?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    https://community.smartsheet.com/discussion/comment/318536#Comment_318536

    Haha I missed the edit to this post! Glad you were able to figure it out.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!