View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Steve[_124_] Steve[_124_] is offline
external usenet poster
 
Posts: 12
Default Find free desk from a list

On Friday, September 29, 2017 at 4:25:48 PM UTC+1, Steve wrote:
I have a worksheet with a list of desk numbers in col A, then across row 3 I have dates.

I want to be able to type a date into a cell and return the desk numbers which are free that day (an unoccupied desk is denoted by the letters "NA")

Desk# Name Last Name Sun 01-Jan Mon 02-Jan
15 Billy Smith NA
3 Jane Jones BH NA
21 Bob Spoggs NA
48 Mary Christmas BH NA
33 Bendor Grosvenor BH BH

For example, if I type in 1/1/2017 I would get 15 and 21 returned; if I Entered 2/1/2017 I'd get 3 and 48 returned.

Any help appreciated!


Hi Claus,

thanks you for the quick and brilliant response. It works perfectly, but in reality I have much more data. I see the formulae are array formulae but assumed that when I inserted rows/columns, or moved the data around the formulae would reflect the moves. This doesn't seem to be the case.


I've added another sheet to your workbook on Onedrive ("Tabelle2") as an example, although there will actually be many more rows of Desks.

Thanks again! It's good to hear from you.

Steve