Creating a Summary Sheet

[Avster]

Hi Guys,

Hoping if someone could help me out as I was unable to find the information from others websites.

I am currently trying to create a 'one stop shop' for all the necessary information required by management from the several excel documents that our division has. Some of the information are exported from our PMS, Excel documents, etc. I guess what I would like to do is the below:

Last night Last year Current (Comparisons)
Room Rate $100 $105 $99
Occupancy 60% 50% 40%

Room upsell $500 $450 $445
Other Revenue $300 $200 $250

Number of staff currently on shift 5 3 7

Is this a macro question or a programming one? I would like this for my own accord so that I am 'in sync' with the operations. Please advise soon.

 

[hiker95]

Avster,

Thanks for the workbook.

Here is a screenshot of the only workbook/worksheet available with instructions:

Excel Workbook
	A	B	C	D	E	F	G
1
2
3			Summary for the 8th Sept
4
5		Current	Previous Year	Budget
6	Occupancy	78.80%	76%	79%
7	Room Rate	$203	$206	$206
8	Room upsell	$30,000	$36,000	$29,000
9
10	Staff on shift	4	5	3
11
12	Question
13	1	I want to be able to pull data in the cell highlighted yellow from different workbooks automatically as opposed to individually opening up the documents and manually inputting them.
14	2	Do I need to setup something in such a way so that at the start of my day, I can pull the relevant information and have a good snapshot?
15	3	Is this Macro?
16
Sheet1

Without the other workbooks/worksheets it is difficult to assist you.


Please click on the Post Reply button, then just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.

 

[Avster]

Hiker,

Thank you for the screenshot... would it help if I uploaded the 2 excel data sheets as well from the info needs to be pulled from?

Please let me know. Thank you for your patience matey.

 

[Avster]

Hi Guys,

I managed to upload the workbook on the box.com site for you and shared them. Hope it makes sense. Please let me know if you need any more info.

Web link: http://www.box.net/shared/vbhjkrn2y0c8xsf13vhc

Cheers, Avster

 

[pedro-egoli]

This is a possible solution.

To test I set up a workbook with worksheets named
stats
upsell
roster
summary

In each of the first three sheets I recorded info applicable to them just in individual cells

Summary sheet looks like this

Excel Workbook
	A	B	C	D
6	Occupancy	71%	76%	80%
7	Room Rate	$216	$206	$206
8	Room upsell	$23,000	$90,000	$25,000
9	*	*	*	*
10	Staff on shift	9	*	*
summary

Pretty basic but it might help with your endeavours

Pedro

 

[Avster]

Hi Pedro,

Thank you for your reply.

Though that sounds appealing, I have downsized the worksheets I have uploaded. Normally they are for an entire financial year which is around 4MB each file, so I would not be able to transfer them onto a single worksheet

Cheers, Avster

 

[Avster]

Bump

 

[Avster]

hey guys, just a quick follow up on this... anyone have any idea please? I am very eager on getting a solution for this
Thank you