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 Steve,

Am Fri, 29 Sep 2017 08:25:44 -0700 (PDT) schrieb Steve:

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.

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgagJLHxcNi8sQok-Fg

Regards
Claus B.
--
Windows10
Office 2016

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

Hi Steve,

Am Fri, 29 Sep 2017 10:31:57 -0700 (PDT) schrieb Steve:

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

have another look. The formulas are on "Tabelle1" and now for all filled
columns in "Tabelle2".


Regards
Claus B.
--
Windows10
Office 2016

Hi Steve,

Am Fri, 29 Sep 2017 10:31:57 -0700 (PDT) schrieb Steve:

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

here is now another version with a macro:
https://1drv.ms/x/s!AqMiGBK2qniTgagLG6yLrbKoh7f90Q


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus,

Thanks yet again! I'll try it over the weekend, I have a party to go to, so will leave it till after that ;o)

Hi Steve,

Am Fri, 29 Sep 2017 11:36:38 -0700 (PDT) schrieb Steve:

Thanks yet again! I'll try it over the weekend, I have a party to go to, so will leave it till after that ;o)

have much fun!
The workbook with the macro you have to download because macros are
disabled in OneDrive.


Regards
Claus B.
--
Windows10
Office 2016

Hi Steve,

Am Fri, 29 Sep 2017 20:40:15 +0200 schrieb Claus Busch:

The workbook with the macro you have to download because macros are
disabled in OneDrive.

there is one line missing into the code. The range in column D must be
cleared before writing the new values.
I changed it in the workbook on OneDrive.


Regards
Claus B.
--
Windows10
Office 2016