Tell me there is a quicker way!!!!

[Krucifire]

Bunnings Trade Card Inbound_Outbound Call Stats (4).xls
	A	B	C	D	E	F	G	H
4		YTDTOTALS	Week8	Week9	Week10	Week11	Week12	Week13
5	Inbound
6	TotalweeklyHoursWorked(8am-6pmdaily)
7	TradeCardQuery
8	GeneralEnquiries	407	18	49	34	30	5	22
9	StatusofPriorApp	1620	50	61	51	37	31	36
10	CancelledCard	246	10	0	1	1	0	4
11	RenewalofCard	2291	11	18	11	14	22	9
12	NewApplication	869	16	13	18	19	17	10
13	TTLInboundTradeCardEnquiries	5433	105	141	115	101	75	81
14	OwnerBuilderQuery
15	GeneralEnquiries	203	9	22	24	21	5	11
16	StatusofPriorApp	349	12	26	19	17	8	10
17	CancelledCard	47	0	0	0	1	3	2
18	RenewalofCard	227	2	4	6	5	7	15
19	NewApplication	138	3	4	7	3	3	2
20	TTLInboundOwnerBuilderEnquiries	964	26	56	56	47	26	40
21	EscalatedCalls
22	TradeCard	163	5	1	7	7	6	1
23	OwnerBuilderCard	41	3	7	0	2	0	0
24	CreditAccount	13	0	1	0	5	0	0
25	TTLInboundEscalatedCalls	217	8	9	7	14	6	1
26	OtherCalls
27	CreditAccount	759	3	17	19	26	15	14
28	BunningsStores	689	7	12	12	6	13	8
29	AllOther	443	16	8	0	9	2	0
30	TTLOtherInboundEnquiries	1891	26	37	31	41	30	22
31
32	TTLInboundEnquiries	8505	165	243	209	203	137	144
33
34	InboundCallSummary
35
36	TTLTradeCardInboundCalls	3083	110	142	122	108	81	82
37	TTLOwnerBuilderCardCalls	368	15	15	14	13	3	8
38	TTLInboundRenewalApplications	1038	0	22	17	19	29	24
39	TTLInboundApplications	542	50	17	25	22	20	12
40	ConversionRatioApps:Calls	0.457838308	0.4	0.248408	0.308824	0.338843	0.583333	0.4
41
42	OutboundCalls
43	TradeCard	1891	27	39	26	46	13	9
44	OwnerBuilderCard	216	10	19	10	13	7	0
45	CreditAccount	113	3	4	6	13	2	0
46	TTLOutboundCallsMade	2220	40	62	42	72	22	9
47
48	MessageBank
49	NumberofMessagesLeft	77	77	0	0	0	0	0
50	MessagesfromBunningsStores/HeadOffice	4334	0	61	47	58	121	85
51	TTLMessagesleft	287	77	8	4	2	14	9
52	Irretrievablenumbers	4554	10	69	51	60	135	94
53	NumberofCallsReturned(Outbound)	1256	89	13	29	17	41	23
54		2789		65	59	55	100	79
55	Non-callActivity
56
57	TradeCardApplicationsProcessed	132	132	0	0	0	0	0
58	TradeCardRenewalsandReplacements	5509	21	106	55	62	39	41
59	TradeCardApplicationsDeclined	1025	27	27	24	23	25	15
60	OwnerBuilderCardApplicationsProcessed	365	36	5	1	17	0	1
61	OwnerBuilderCardRenewalsandReplacements	1138	15	31	13	26	8	41
62	OwnerBuilderCardApplicationsDeclined	125.2307692	8.230769	6	10	4	10	17
63	TTLApplicationsReceived	298	231	2	0	1	0	0
Summory

Ok if you look at the formulas above you will see how they are working its basically only the sheet name that is changing.

I have to do this formula for 170 weeks, and i REALLY REALLY REALLY dont want to do it 1 by 1... is there a quicker way to reference this data????

 

[Fazza]

You're right. There is a quicker way. There are many quicker ways.

Simplest change from what you have now might be to use INDIRECT. So populate a row with the worksheet names (either as text or generated by formula) say in row 1 and then all the formula with indirect would get the required value. Can you do this?

Using some VBA to do the work would be a better option. Set up a loop to go through the worksheets and if OK just put the values in the summary, that is, get rid of the formulas altogether. Would this be acceptable?

Better again if OK would be to have all the data in a single database-type table instead of 170 worksheets. Can you do this?

What is best for you?

 

[stanleydgromjr]

Krucifire,

If I understand you correctly.

Try this:

Excel Workbook
	A	B	C	D	E	F	G	H
1			C thru FP = 170
2		Weeks	1	2	3	4	5	6
3
4		YTD*TOTALS	Week*8	Week*9	Week*10	Week*11	Week*12	Week*13
5	Inbound*	Thru Week
6	Total*weekly*Hours*Worked*(8am*-*6pm*daily)	1
7	Trade*Card*Query
8	General*Enquiries	18	18	49	34	30	5	22
9	Status*of*Prior*App	50	50	61	51	37	31	36
10	Cancelled*Card	10	10	0	1	1	0	4
11	Renewal*of*Card*	11	11	18	11	14	22	9
12	New*Application	16	16	13	18	19	17	10
13	TTL*Inbound*Trade*Card*Enquiries	105	105	141	115	101	75	81
14	Owner*Builder*Query
15	General*Enquiries	9	9	22	24	21	5	11
16	Status*of*Prior*App	12	12	26	19	17	8	10
17	Cancelled*Card	0	0	0	0	1	3	2
18	Renewal*of*Card*	2	2	4	6	5	7	15
19	New*Application	3	3	4	7	3	3	2
20	TTL*Inbound*Owner*Builder*Enquiries	26	26	56	56	47	26	40
Sheet1

As you change the value in cell B6, the SUM formulae in B8, down, will adjust for the weeks you want to sum.

The formula in cell B8 (copied down):
=SUM(OFFSET($C8,,,,$B$6))


Have a great day,
Stan

 

[stanleydgromjr]

Krucifire,

As you change the value in cell B6, the SUM formulae in B8, down, will adjust for the weeks you want to sum.

Change B6 from 1 to 5:

Excel Workbook
	A	B	C	D	E	F	G	H
1			C thru FP = 170
2		Weeks	1	2	3	4	5	6
3
4		YTD*TOTALS	Week*8	Week*9	Week*10	Week*11	Week*12	Week*13
5	Inbound*	Thru Week
6	Total*weekly*Hours*Worked*(8am*-*6pm*daily)	5
7	Trade*Card*Query
8	General*Enquiries	136	18	49	34	30	5	22
9	Status*of*Prior*App	230	50	61	51	37	31	36
10	Cancelled*Card	12	10	0	1	1	0	4
11	Renewal*of*Card*	76	11	18	11	14	22	9
12	New*Application	83	16	13	18	19	17	10
13	TTL*Inbound*Trade*Card*Enquiries	537	105	141	115	101	75	81
14	Owner*Builder*Query
15	General*Enquiries	81	9	22	24	21	5	11
16	Status*of*Prior*App	82	12	26	19	17	8	10
17	Cancelled*Card	4	0	0	0	1	3	2
18	Renewal*of*Card*	24	2	4	6	5	7	15
19	New*Application	20	3	4	7	3	3	2
20	TTL*Inbound*Owner*Builder*Enquiries	211	26	56	56	47	26	40
Sheet1

Have a great day,
Stan

 

[Krucifire]

The second is probably the only thing i may be capable of doing, if there was some more info given as to how it would be done.

I am capable of created the data base to hold the data how ever, the people recording the data and populating this spread sheet are in no way computer savy, hence why there is 170 odd spread sheets that already contain data as all the know how to do is data entry, getting them to use Excel to do this was a mission in its self. I am fully aware that the way we are tracking this data is the a very inefficient way of doing it. i would like to get it to a point were all this data is captured without them lifting a finger.

Currently i am able to track all the call data so this is able to be automatically populated. given the right set of formulas(not done as yet still entered manually)

Anyway slightly off track, back to where we started i think the second option is probably the best option given our circumstances.

 

[Krucifire]

Hey Stan,

i think you have maybe missed the point on this one, or possibly more likely im not understanding correctly what your suggestion is doing.

I looks as thou your formulas are operating off the values in sample i posted, However these Values are obtained off other spreadsheets, will your formulas pull the data off the other sheets that were not shown in the sample data???

 

[SydneyGeek]

EDIT: Got held up making my response so I'm late but, FWIW, here's another option.

Yep. You need to add some helper data in row 3 which will be all the sheet names,
and a helper column in column A with the text J13 in cell A8. Fill down to get the otehr cell refs, then...
D8 is =INDIRECT("'"&D$3&"'!"&$A8)

Copy down and across.

Note: There is no need for array formulas in these cells.

Denis

 

[Krucifire]

Thx Denis Stan and Fazza,

Im going to give it a shot atm so bare with me next reply prob be in about 20min, im not 100% sure i fully understand the concept behind what your suggesting but only way to find out is try it out, ill do a couple of test cells and see how we go.

Posting in back soon

- Troy

 

[Krucifire]

Yep sorry guys this is beyond my knowledge i have no idea how it picks up the info from the other sheets,

Could you please show me how you would write the cell formulas for cells B8 B9 B10 and C8 C9 C10, i should be right from there.

Thanx

 

[SydneyGeek]

Hi Troy,

To explain my approach...
In row 3 you put all the worksheet names (ie, WEEK 8 WC 05.02.07 in C3, WEEK 9 WC 12.02.07 in D3, etc)
Then, add a helper column to the left of column A -- this now becomes the new Column A.
Start in A8 and enter the text J13. Select the cell and drag down to the last used row, which will give you J9, J10, etc

OK -- now you have sheet names in Row 3, cell addresses in Column A, and the first data cell that you want to pull data to is D8.
The formula I posted does that. INDIRECT effectively lets you build a sheet / cell reference from the values in other cells, and get the value of the cell with that cell reference.

Hope that helps

Denis