Cross sheet referencing without limitations


Hi

I need to cross link these 2 data sheets.

Sheet 1 is the raw data entry sheet.

Sheet 2 is the summary of sheet 1. I need to link area in sheet 1 with the rows in sheet 2.

I had success with countifs area =100 and first draft = 1 (checked). I ran into a cross sheet limitation error. I am aware of the 100 unique limitation which I think I reached.

I tried using vlookup and I have continuously received a error message of some sort.

Sheet 1

Sheet 2


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Martha Talenga

    COUNTIFS is what I would use here as well. You should only need as many references as you have columns in your Sheet 1. Do you have over 100 columns that you're needing to look at?

    If you're reaching the 100 references error, I would check to see how many references you've created in that sheet in case there are ones not being used (right click on a cell > Sheet Reference Manager).

    Then delete any references that may have been for testing or for something else. See: View, modify, or delete cross sheet references in formulas

    As a side-note, an easier way to write your formulas would be to have the First Row in your second sheet re-list out the column names you're looking for (e.g. "100" in the top row). Then you can reference that cell instead of manually typing in the number each time, and drag-fill the formula across the row so it updates for that row based on what's written above:

    =COUNTIFS({Area Column}, [100]$1, {First Draft Column}, 1)

    when you drag this to the right one column, it should then update:

    =COUNTIFS({Area Column}, [115]$1, {First Draft Column}, 1)


    Then for your next row down, swap out the {reference} for the checkbox:

    =COUNTIFS({Area Column}, [100]$1, {Site Verified Column}, 1)


    Let me know if using the Sheet Reference Manager helped!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Martha Talenga

    COUNTIFS is what I would use here as well. You should only need as many references as you have columns in your Sheet 1. Do you have over 100 columns that you're needing to look at?

    If you're reaching the 100 references error, I would check to see how many references you've created in that sheet in case there are ones not being used (right click on a cell > Sheet Reference Manager).

    Then delete any references that may have been for testing or for something else. See: View, modify, or delete cross sheet references in formulas

    As a side-note, an easier way to write your formulas would be to have the First Row in your second sheet re-list out the column names you're looking for (e.g. "100" in the top row). Then you can reference that cell instead of manually typing in the number each time, and drag-fill the formula across the row so it updates for that row based on what's written above:

    =COUNTIFS({Area Column}, [100]$1, {First Draft Column}, 1)

    when you drag this to the right one column, it should then update:

    =COUNTIFS({Area Column}, [115]$1, {First Draft Column}, 1)


    Then for your next row down, swap out the {reference} for the checkbox:

    =COUNTIFS({Area Column}, [100]$1, {Site Verified Column}, 1)


    Let me know if using the Sheet Reference Manager helped!

    Cheers,

    Genevieve

  • Hi Genevieve

    Thanks for the feedback, it has been somewhat helpful for the first part of the my problem. I have followed your advise and referenced the columns using COUNTIFS and this has worked thanks.

    I have also developed a dashboard.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!