Help with a Formula Please :)

[Legacy 237453]

Hi Peeps

I am im the process of designing a daily report template for the company i work for.
at the top we manually type who is working on the dayshift and on the nightshift.

We run 4 shifts fo 4 people.

Is there any way for these cells to populate themselves? We have a rota in excel but its usually closed so im not sure if i can use VBA to take data from that workbook?

Any other suggestions?

Thanks as always!
Craig

 

[Akashwani]

Hi Craig.

How is your rota laid out in your "closed" Workbook? Something like this perhaps?...

Example Rota Table (Closed Workbook)...

Excel Workbook
	A	B	C	D
1	Names	Shift	Dates
2	Bill	A	16/04/2013
3	Tom	B	16/04/2013
4	Sid	C	16/04/2013
5	Bud	D	16/04/2013
6	Bill	B	17/04/2013
7	Tom	C	17/04/2013
8	Sid	D	17/04/2013
9	Bud	A	17/04/2013
10	Bill	C	18/04/2013
11	Tom	D	18/04/2013
12	Sid	A	18/04/2013
13	Bud	B	18/04/2013
14	Bill	D	19/04/2013
15	Tom	A	19/04/2013
16	Sid	B	19/04/2013
17	Bud	C	19/04/2013
18
Rosta

I have created this Sample Data based on the above Sample Rota Table...

Excel Workbook
	A	B	C	D
1	Name	Shift	18/04/2013
2	Bill	C
3	Tom	D
4	Sid	A
5	Bud	B
6
Sheet3

The following are Named Ranges...
RotaNames
RotaShift
RotaDates

If you can provide some details of the layout of the Closed Workbook, eg.. Ranges used, Headers, cell contents etc, that would be very helpful.
Also, if you can provide an example of your expected results in the Open Workbook, that would help us to understand the direction we need to be going in.

I hope this helps.

Ak

 

[Legacy 237453]

Hi

Thanks so much for your time and effort, I've finished work now and won't be in for another 12 hours.... Just thought I'd message to let you know I wouldn't want you to think its been wasted.... As soon as I get to work ill test it and try to supply some sample data

again thanks a lot!
cheers
craig

 

[Akashwani]

Hi Craig,

Thanks for the feedback, it is really considerate of you and appreciated.
I look forward to your follow up on this.

Ak