Extracting data based on date

[mattyn]

Hi All

Firstly I hope everyone is staying safe and well.

Todays problem - straightforward spreadsheet that identifies a student accommodation planner. A number of worksheets for a number of accommodation venues.

Each worksheet consists of Columns A to L with specific info about the particular accommodation and its resident.

Col M onwards is the date planner - with each column as one day - and Row 9 has the date - so M9 is 1 Jun 20, N9 is 2 Jun 20 etc. The student and accom details are from row 10 onwards, and the accommodation team populate columns m onwards with a R if the student is resident in the block. I use =countif(M10:M500,"R")

Col M6 onwards count how many residents are in the accommodation for that day.

I want a Headlines Front Page that tells me the total # of residents in each venue today, in 7, 14, 21 days time - as a forecast.

So as columns on the headlines page - I need under the following headers:

Accom Venue	Total capacity	Resident Today	Resident + 7 days	Resident +14 days	Resident +21 Days
Text	Text	to be pulled from the ind worksheet count for today	pulled from the ind worksheet count for today + 7	pulled from the ind worksheet count for today +14	pulled from the ind worksheet count for today +21

Have no idea if this is possible - would appreciate some guidance please. Just hope this makes sense.

Very many thanks and kindest regards

matt

 

[MUKESHY12390]

Hey Mattyn-

You explain in detail but I couldn't get everything. could you share sample file input file with target output.

whatever it is can be done.

 

[mattyn]

Hi - and thank you so much for picking this up. Can't add the file as it is on work PC but hopefully show well enough in a table.

So - main worksheet called "ACCOM"- all typed in static data. The rows of Days and dates goes on rightward for ever!! I have Freeze Panes just under 1 Jun 20 - and there are over 1600 rooms listed down the left side.

				Mon	Tue	Wed	Thu
Name	Entry Date	Leaving Date	Accom Number	1 Jun 20	2 Jun 20	3 Jun 20	4 Jun 20
Joe Smith	2 Jun 20	20 Jun 20	1-1-1	E	O	O	O
Bob Colgees	30 May 20	20 Aug 20	1-1-2	O	O	O	O
James Bond	1 Jan 20	2 Jun 20	1-1-3	O	E	E	E
			1-1-4	E	E	E	E
			1-1-5	R	R	R	E

Where E = Empty, O = Occupied, R = Under repair

I would like a HEADLINES worksheet that gives the general accommodation status, pulling and calculating data from the ACCOM worksheet as follows: (Have reviewed the headers as follows - ideally needs to be only these four - and not static dates as above).

	=Today()	=Today()+7	=Today()+30	=Today()+60
Occupied Rooms	count the columns that are indicated "O" under todays date	count the columns that are indicated "O" under todays date + 7 days	count the columns that are indicated "O" under todays date + 30 days	count the columns that are indicated "O" under todays date + 60 days
Empty Rooms	count the columns that are indicated "E" under todays date	count the columns that are indicated "E" under todays date + 7 days	count the columns that are indicated "E" under todays date + 30 days	count the columns that are indicated "E" under todays date + 60 days
Rooms Under repair	count the columns that are indicated "R" under todays date	count the columns that are indicated "R" under todays date + 7 days	count the columns that are indicated "R" under todays date + 30 days	count the columns that are indicated "R" under todays date + 60 days

I hope this explains what I am trying to do.

Many many thanks again for helping - it is really much appreciated.

Matt

 

[MUKESHY12390]

okay.. so here is the formula which could bring the required details.

formula to get details for "Today"
-----------------------------------------------------------------------------
=COUNTIFS(INDEX(ACCOM!$2:$1048575,,MATCH(TODAY(),ACCOM!$2:$2)),"O")

ACCOM!$2:$1048575    --- >  this is the full range for your data in "Accom" sheet where "$2" is the  row which contains "Date" and $1048575  is nothing but last row.


TODAY() - - today date

ACCOM!$2:$2  --- > again row which contains the Date


,"O" ---> Criteria which represent Occupied

---------------------------------------------------------------

Do TODAY() + 7 , TODAY() +30 and TODAY() +60 for all other 3 columns, rest formula would be same.


revert back in case of any doubt.

 

[mattyn]

Hi Mukeshy12390

Absolutely awesome - thank you so much.

Another Q if I may.

I want to duplicate in cell E3 (under Status) whatever the accommodation state is for today: e.g.

					Tue	Wed	Thu
Name	Entry Date	Leaving Date	Accom Number	Status	2 Jun 20	3 Jun 20	4 Jun 20
Joe Smith	2 Jun 20	20 Jun 20	1-1-1	duplicate whatever is in this row under today() --->	O	O	O
Bob Colgees	30 May 20	20 Aug 20	1-1-2	"	O	O	O
James Bond	1 Jan 20	2 Jun 20	1-1-3	"	E	E	E
			1-1-4	"	E	E	E
			1-1-5	"	R	R	E

If that makes sense...??

VMT

Matt

 

[MUKESHY12390]

I'm glad it worked out.

I want to duplicate in cell E3 (under Status) whatever the accommodation state is for today: e.g.

I didn't understand , could give another example.

 

[mattyn]

Hi MUKESHY12390

Hopefully this explains better.

Under each date and against each accommodation is a status - Where E = Empty, O = Occupied, R = Under repair etc.

I want a static column that will copy the status for today - to make it easier for the user to see that status for that accommodation without scrolling across to find today within the dates.

Accomm Number	Status Today*	20 Jun 20	21 Jun 20	22 Jun 20	23 Jun 20	24 Jun 20	25 Jun 20	26 Jun 20	27 jun 20
1-1-1	O	O	O	O	O	O	O	O	O
1-1-2	E	E	E	E	E	E	O	O	O
1-1-3	R	R	R	R	R	E	E	E	O

*copied from the corresponding cell on this row under todays date. Therefore tomorrow (24 Jun 20) it will show:

Accomm Number	Status Today	20 Jun 20	21 Jun 20	22 Jun 20	23 Jun 20	24 Jun 20	25 Jun 20	26 jun 20	27 Jun 20
1-1-1	O	O	O	O	O	O	O	O	O
1-1-2	E	E	E	E	E	E	O	O	O
1-1-3	E	R	R	R	R	E	E	E	O

Does that explain better?

Matt

 

[MUKESHY12390]

got it, there are several ways you can do it.

this is one of the.

=HLOOKUP(TODAY(),$2:$1048576,ROWS($E$2:E2)+1,0)

$2:$1048576 ---> Entire data range including Date header

ROWS($E$2:E2)+1 ---> $E$2 would be Any Date cell

0 = for exact match.

 

[mattyn]

Hi again Mukeshy12390

Absolutely awesome - so thank you so much.

 

[mattyn]

One last one (well two) - I promise.

Have added the list of residents to the worksheet which includes a column that states the date of birth of the resident in the format dd-mmm-yy. From this I would like to do two things:

One: I want to highlight (by simple colour) the surname of any of the residents that have a birthday in the next 30 days. (Surname is Col B, Date of birth is Col F)
Two: I would like a cell that calculates how many birthdays there will be from within the list of residents in the next 30 days.

Again, very many thanks in advance. You have been a great help.

Matt