Google Sheets: How to pull row numbers from different sheets based on date (Solved)

[qcguys]

I have a mastersheet, with the help of someone on here before I learned how to pull entire rows of data and put that data into tables on a mastersheet.

right now I am physically going through each sheet, copying the row numbers for a specific date, and putting those row numbers on the mastersheet so it can use them as reference.

I am printing out the data at the end of the day for that day.

If I could use date to pull the row numbers from specific sheets ON the mastersheet that would be great.

if there is another solution please let me know.


some problems you may face: my dates are put into the sheets in the form of date time

some grace: the tables on the mastersheet dont have to all be filled out just the data from that date has to be on there (ie: there can be blank tables when there isnt enough data to fill them)


Solved!!!

[AliGW]

And this is for Google Sheets, is it?

[qcguys]

yes it is, however, so far it has been compatible with excel solutions

[AliGW]

It's important for helpers to know that the solution needs to work with Google Sheets, so I've moved you to the correct forum section and adjusted your thread title.

[JEC.]

Instead of using indirect, use 3 different formulas for each sheet. Indirect makes your workbook very slow

I don't know if Google sheets has dynamic formula's like FILTER, but below should work. Drag them all to the right.

In H2:

Please Login or Register  to view this content.

In H3

Please Login or Register  to view this content.

In H4:

Please Login or Register  to view this content.

[qcguys]

so I don't know where the problem lies, but I did paste these 3 formulas into the corresponding cells and dragged them to the right and I only got #REF in the tables. (On google sheets)

[qcguys]

I also just realized the excel download has the date time blocked out

[JEC.]

These formula's work in Excel desktop versions

[qcguys]

they do!! (only in excel not google sheets but still gives me hope) I just tried it. I have to use google sheets still sadly because the data is pulled from google forms

[qcguys]

=TRANSPOSE(FILTER(ROW(Secondary!B2:B20),Left(Secondary!B2:B20,10)=Left(I1,10)))
the above pasted into cell H3 on master sheet
this one works on google sheets for dates 12/29/21 12/30/21 & 12/31/21, but I cant get it to work for the date 1/1/2022



=IFERROR(SMALL(IF(INT(Secondary!$B$2:$B$100)=$I$1,ROW($2:$100)),COLUMN(A1)),"")
the above pasted into cell H3 on master sheet
this one works on excel for ALL DATES but not on google sheets

[JEC.]

And what if you write like this?

Please Login or Register  to view this content.

[qcguys]

It worked!!!!!!!!!!!!!!!!!!!!!!! Yes thank you

[JEC.]

Nice, good luck!

[janmorris]

another method, specifically for Google Sheets (due to the ARRAYFORMULA and SPLIT functions):

Please Login or Register  to view this content.