Formula Problem

[nickharr]

I would be very grateful if anyone could help me with this problem.

I have two worksheets - one containing information about bookings for our holiday let - Column A is the date they arrive, column B is the date they depart and column E is their name. This sheet is named "Bookings". The second sheet is named "Calendar" and brings foward the details from "Bookings". The formula I use (which someone from this forum kindly helped me with) is =IFERROR(INDEX(Bookings!$E$2:$E$500,MATCH(1,(Bookings!$A$2:$A$500<=A420)*(Bookings!$B$2:$B$500>=A420),0)),""). A420 contains a date of 1/1/2021. The formula has worked perfectly - any bookings added to the "Bookings" sheet have come over to the "Calendar". However, none of the details from 1/1/2021 show on "Calendar". I have checked and rechecked the date format and it is the same that I used in 2020. I can't understand why a new year would make a difference. Would anyone have any idea why this formula isn't working for next year's bookings?

I hope I have given enough details and thank you in advance of any suggestions.

 

[Puertorekinsam]

Stupid question, your new year data isn't in rows 501 or larger are they?

 

[Toadstool]

Hi Nickharr,

Try entering the formula with Ctrl-Shift-Enter so Excel puts curly brackets around it.

 

[Trixterz]

You may want to look into creating custom dynamic fields through the name manager as your Booking is only limited to a max row of 500.

 

[nickharr]

Hi Guys

Thanks for your replies.

The data is still well under row 500 so I think that should be okay.

I had done the Shift Ctrl Enter for the formulas so again, I don't think that is a problem.

All the data I need is contained within rows 2 and 236 so again I presume the 500 is sufficient.

Thanks again for your help and suggestions.

 

[Toadstool]

Works for me if I use Ctrl-Shift-Enter

NickHarr.xlsx
	A	B	C	D	E
1	Start	End			Name
2	12/28/2020	12/29/2020			Oldcust
3	12/30/2020	1/3/2021			Newcust
4	2/2/2021	3/3/2021			Latercust
5
Bookings

NickHarr.xlsx
	A	B
415	12/27/2020
416	12/28/2020	Oldcust
417	12/29/2020	Oldcust
418	12/30/2020	Newcust
419	12/31/2020	Newcust
420	1/1/2021	Newcust
421	1/2/2021	Newcust
422	1/3/2021	Newcust
423	1/4/2021
424	1/5/2021
Calendar

Cell Formulas
Range	Formula
B415:B424	B415	=IFERROR(INDEX(Bookings!$E$2:$E$500,MATCH(1,(Bookings!$A$2:$A$500<=A415)*(Bookings!$B$2:$B$500>=A415),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Select all the cells that contain dates & change the format to General. Do all the dates change to numbers like 44197?

 

[nickharr]

Hi again guys

I have found the problem but I'm not quite sure why it caused the problem.

At the end of the dates in 2020 in "Bookings" sheetI had inserted an extra row to make additions for the full year. When I deleted this row it worked fine and also when I added the row back again it still worked fine.

Anyway I do appreciate all your help and suggestions.

 

[Fluff]

Glad you sorted it & thanks for the feedback.