Need to access sheet column by date

[John Keith]

I'm building a small Excel table to provide accomodation availability information. I want to have a column which extracts the current days availability from a separate worksheet in the same workbook.Does not need to be any detail, just a Y/N result.

Is this possible?

Thanks

John

[AliGW]

Welcome to the forum.

Yes, it's possible. Can't say more than that based on such scant information.

[John Keith]

Great thanks,

On the main worksheet I just want to have a column which shows if a property has a vacancy that day.
The data will be drawn from a worksheet which list the availability for the next seven days.

So what I want is that Column F on the front sheet will look up whatever today's date is on the relevant property sheet by using the
DATE functionality to identify which cells to take the information from.
So if any one of the rooms has availability the front page cell will contain 'Y', if none of the rooms are available then the front
page cell will contain an 'N'.

Hope this is clearer.

A stripped out version of the file should be attached - but can't see any indication of that on this page!

John

[FlameRetired]

Hello John. Welcome to the forum.

Since the upload didn't contain any indications of occupancy referencing and matching a target date with this formula is as far as I could take this. Formula will obviously need to be 'wrapped' in an index reference, but this takes care of the match part. With the target date in cell B1 use this formula in F3.

Please Login or Register  to view this content.

[AliGW]

I would go further with this:

=IF(SUMPRODUCT((INDIRECT("'"&[@[Property name]]&"'!C15:BR15")=TODAY())*(INDIRECT("'"&[@[Property name]]&"'!A17:A60")<>"")*(INDIRECT("'"&[@[Property name]]&"'!C17:BR60")=""))>0,"Y","N")

If you want to return the number of rooms available, then this:

=SUMPRODUCT((INDIRECT("'"&[@[Property name]]&"'!C15:BR15")=TODAY())*(INDIRECT("'"&[@[Property name]]&"'!A17:A60")<>"")*(INDIRECT("'"&[@[Property name]]&"'!C17:BR60")=""))