How to look up matching date from table and roll on

[Jar888]

Hi there, I'm pretty average when it comes to excel but I like to try and figure things out myself. I've explored a few options, but I couldn't figure it out quite on my own.

At the moment, we have several worksheets we work from at work. I've simplified a LOT of what we already do with data entry, as previously we copy/pasted way more than what was necessary.

I currently pull a sheet from one book to our master book, this book has delays in projects etc. that we have to input. Is there a way with 'index match array' that I can possibly pull an entire row(s) based off of the new updated sheet?

Currently the pulled data from the old book goes into "Raw Data CB Delays" and I have to copy/paste the data to "Delays CB". In the raw data there could be a range of 3 - 30 delays (Varies daily) and has to pull the data of 13 columns over so that other formulas in the master spreadsheet will continue working.

Please let me know if this makes sense, I've attached a few snips for you all to have a gander.

The second question I have would be weather we can roll this on so it automatically updates every time we refresh data/calculations?

Cheers!

Jar

 

[Toadstool]

Hi Jar888,

So you want to pull up to 30 rows of data from Raw Data CB Delays into Delays CB for a specific date?

Jar888.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	NS	Rig	Shift	Location	Job	Breakdown	UPD	Fault
2	15-Jan-22	NS	R2	S1	Loc74	A	Roller	G	Cat among pigeons
3	15-Jan-22	DS	R3	S3	Loc111	A	Packer	H	Bob fell asleep
4	16-Jan-22	NS	R4	S1	Loc148	A	Lifter	L	Mouse nest
5	16-Jan-22	DS	R5	S3	Loc185	B	Dropper	X	Power cut
6	16-Jan-22	NS	R6	S1	Loc222	B	Roller	G	Spanner in works
7	16-Jan-22	DS	R7	S3	Loc259	B	Packer	H	Bob asleep again
8	16-Jan-22	NS	R8	S1	Loc296	B	Lifter	L	Constants weren't
9	17-Jan-22	DS	R9	S3	Loc333	A	Dropper	X	Variables wouldn't
Raw Data CB Delays

Jar888.xlsx
	A	B	C	D	E	F	G	H	I
1	For date=	16-Jan-22
2
3	Date	NS	Rig	Shift	Location	Job	Breakdown	UPD	Fault
4	16-Jan-22	NS	R4	S1	Loc148	A	Lifter	L	Mouse nest
5	16-Jan-22	DS	R5	S3	Loc185	B	Dropper	X	Power cut
6	16-Jan-22	NS	R6	S1	Loc222	B	Roller	G	Spanner in works
7	16-Jan-22	DS	R7	S3	Loc259	B	Packer	H	Bob asleep again
8	16-Jan-22	NS	R8	S1	Loc296	B	Lifter	L	Constants weren't
9
Delays CB

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A4:I8	A4	=IFERROR(INDEX('Raw Data CB Delays'!A$2:A$99999,AGGREGATE(15,6,ROW('Raw Data CB Delays'!$A$2:$A$99999)-ROW('Raw Data CB Delays'!$A$1)/('Raw Data CB Delays'!$A$2:$A$99999='Delays CB'!$B$1),ROW()-ROW($A$3))),"")

 

[Jar888]

I can see how that would work, but the information is on a pivot table on Raw Data CB Delays. It doesn't quite pull the information out, and this is what I've been stuck on. Sorry, I should have made that clear.

 

[Jar888]

Ignore me, I'm dumb. I just realized I was trialing it out on an older sheet, so had to adjust the date on what I wanted the spreadsheet to look for. Feel like an idiot now.

Cheers.