Cross sheet referencing without limitations

Martha Talenga

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

Genevieve P.

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

Genevieve P.

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

Martha Talenga

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.