Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 29th 17, 04:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default Find free desk from a list

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!

  #2   Report Post  
Old September 29th 17, 04:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Find free desk from a list

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
  #3   Report Post  
Old September 29th 17, 06:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
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
  #4   Report Post  
Old September 29th 17, 07:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Find free desk from a list

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
  #5   Report Post  
Old September 29th 17, 07:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Find free desk from a list

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


  #6   Report Post  
Old September 29th 17, 07:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default Find free desk from a list

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)
  #7   Report Post  
Old September 29th 17, 07:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Find free desk from a list

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
  #8   Report Post  
Old October 1st 17, 10:25 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Find free desk from a list

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
  #9   Report Post  
Old October 2nd 17, 10:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2017
Posts: 12
Default Find free desk from a list

On Sunday, October 1, 2017 at 10:25:10 AM UTC+1, Claus Busch wrote:
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


Hey Claus, this is fantastic. Thank you very much for getting it done for me, it's much appreciated.

Steve


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Before I jump off my desk... drumbumuk Excel Discussion (Misc queries) 4 October 9th 08 07:16 PM
where do i find the 10 free sessions Natalie New Users to Excel 1 October 12th 06 01:24 AM
Where can I find a free widget to ... Speedmaster Charts and Charting in Excel 2 February 22nd 06 05:23 PM
where can I find a free excel download to use cwood Setting up and Configuration of Excel 6 July 29th 05 04:19 AM
Macro to find the next free line Madasu Excel Programming 5 August 26th 04 02:08 AM


All times are GMT +1. The time now is 12:20 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017