Updating a Summary Sheet from other sheets

[healey21]

I have been presented with a challenge! I have been asked to see if it is possible to work on a summary sheet during a month and look at a sheet name that reflects the current month. taking some data and adding it to the summary sheet. The sceanrio is as follows:

Data is worked on a sheet during that month showing store Code, sales amount, Sales current values and some other information, on the summary sheet I am only interested on the above headings, I would like the store number and amounts to be placed to the right of the last columns but the store codes aren't awlays in order.

My examples are as follows:

Excel Workbook
	A	B	C	D	E	F	G
1	these are fields that do not change					these figures change monthly
2	Store Name	Country	Region	Store Status	Store Code	Sales Amount	Sales Count
3	KFC	USA	NA	Removed	4382	$ 75,865.00	1,543.00
4	Wendy's	USA	NA	Fine	3214	$ 172,750.00	791.00
5	Meiji	Japan	AP	In Progress	7291	$ 73,151.00	401.00
6	Mc Donalds	Mexico	LA	Removed	1782	$ 77,337.00	1,916.00
7	H&M	Sweden	EU	Removed	2819	$ 92,937.00	319.00
8	Mr Bean	Singapore	AP	In Progress	2739	$ 186,279.00	1,611.00
9	Baskin Robbins	Malaysia	AP	Fine	8313	$ 194,360.00	375.00
10	Zara	Spain	EU	In Progress	6281	$ 161,033.00	586.00
11	Samsung	Korea	AP	In Progress	1234	$ 137,644.00	214.00
12	Lenovo	China	AP	Fine	7283	$ 142,656.00	1,196.00
13	Movenpick	Switzerland	EU	Waived	3729	$ 78,251.00	296.00
Mar-10

Summary Sample

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Dec-09			Jan-10			Feb-10
2	Store Name	Country	Region	Store Status	Store Code	Sales Amount	Sales Count	Count/Amount Ratio	Sales Amount	Sales Count	Count/Amount Ratio	Sales Amount	Sales Count	Count/Amount Ratio
3	KFC	USA	NA	Removed	4382	$ 298,731.11	2,983	1.00%	$ 86,460.00	1,600	1.85%	$ 176,291.00	1446	0.82%
4	Mc Donalds	Mexico	LA	Removed	1782	$ 37,281.28	2,138	5.73%	$ 195,583.00	1,081	0.55%	$ 28,463.00	1336	4.69%
5	H&M	Sweden	EU	Removed	2819	$ 37,913.27	829	2.19%	$ 61,666.00	1,539	2.50%	$ 17,995.00	671	3.73%
6	Mr Bean	Singapore	AP	In Progress	2739	$ 161,927.28	10	0.01%	$ 38,339.00	1,462	3.81%	$ 105,416.00	689	0.65%
7	Baskin Robbins	Malaysia	AP	Fine	8313	$ 2,729.23	27	0.99%	$ 142,388.00	1,941	1.36%	$ 92,859.00	773	0.83%
8	Samsung	Korea	AP	In Progress	1234	$ 27,349.24	231	0.84%	$ 9,008.00	1,900	21.09%	$ 36,658.00	742	2.02%
9	Wendy's	USA	NA	Fine	3214	$ 7,219.23	489	6.77%	$ 126,040.00	609	0.48%	$ 47,844.00	556	1.16%
10	Meiji	Japan	AP	In Progress	7291	$ 13,943.41	2,139	15.34%	$ 16,321.00	1,555	9.53%	$ 94,355.00	1812	1.92%
11	Movenpick	Switzerland	EU	Waived	3729	$ 13,943.41	2,139	15.34%	$ 101,774.00	1,422	1.40%	$ 134,008.00	233	0.17%
12	Zara	Spain	EU	In Progress	6281	$ 13,943.41	2,139	15.34%	$ 152,351.00	870	0.57%	$ 188,368.00	844	0.45%
13	Lenovo	China	AP	Fine	7283	$ 13,943.41	2,139	15.34%	$ 192,550.00	1,662	0.86%	$ 119,183.00	1552	1.30%
Master

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o>Any suggestions or guidance would be wonderful.</o>
<o></o>

 

[Special-K99]

Try this to start with

in Master sheet F3
=VLOOKUP(A3,INDIRECT("'"&F1&"'!A3:G13"),6,FALSE)
in Master sheet G3
=VLOOKUP(A3,INDIRECT("'"&F1&"'!A3:G13"),7,FALSE)
in master Sheet H3
=G3/F3

Now copy down the columns and across for each month required.

This solution assumes the following:
1) For each month on the Master sheet there is an equivalent Lookup sheet, ie sheets Dec-09, Jan-10, Feb-10 etc exist
2) The name of the monthly lookup sheet is in cell F1. If its in G1 or H1 change this in the formula.
3) Store name is unique, ie there is only ever one KFC in the list. If this is not so you'll have to modify the formulas but this is a start towards what youre after

 

[healey21]

It comes back with a #ref! error, at the first attempt. would it be possible to explain this formula then I can look to see if I can correct it.

I have re read your comments and will see if I can amend this. The dates in row 1 are in merged cells.

Thankyou for looking at this

 

[healey21]

I had to adjust my spreadsheet slightly but have managed to work it through thank you.

I will now look to see if I can automate the process.

Very Grateful