cell reffrence changing when rows added

Simran Curry

Hello all, i have a strange situation here. i have a sheet where we track all the POs that we go out and pick up from different vendors. in this sheet i have added some sheet summary fields, whhc count the number of total POs, POs picked up and PO s not picked up.

i have made a dashboard to show these 3 things i mentioned above. the catch i have is that i want it to show only the data for a selected date.

in order to filer by the date, i created a helper sheet where i added just one column, called it CHoose date, and made a form out of it. I added this form to the dashboard where whoever is checking can enter a date and submit the form. now in my summary sheet field in my master sheet, i used COUNTIFS and added a condition that counts the POs for dates equal to the date submitted in the helpersheet.

issue is that when i reference the whole column, it shows answer 0 as there are multiple dates in the choose date row. when is reference specific cell, it works for the first time but when anyone submits a new date the cell reference shifts. how do i make sure that the reference in the helpersheet only looks at the very first cell in top.

Paul Newcome

Your cross sheet reference would need to be updated to pull from the entire column, and then you would use an INDEX function like so:

INDEX({Full Date Column}, 1)