linking worksheets (linking cell B4 in worksheet1 to worksheet 5 and so on)

Originally Posted by fasi

Hi,
I'm not a expert on excel but hope someone can make this problem easier for me.

I have 10 customers, each named under a separate worksheet, therefore i have 10 worksheets.plus 1 sheet for summary of all the sheets.
Each customers worksheet has got 5 columns and 5 rows.Date in column A so 4 weeks will have 4 rows of dates under column A. column B is headed as previous balance. column c is headed as bill for this week.column D is headed as total balance.column E is amount paid this week.
In the summary sheet i have got customers names in column A and the rest of the columns are week dates. so if the summary is of the last 2 months then i will have 8 columns dated from example sep06 to nov06.

I want to link the bills for each customer to the summary worksheet so i can calculate the total sales for 2 months. i have already tried to do this by copying a bill from a customer and paste link to summary worksheet. but by doing this i have to individually copy each cell and paste link it to the summary worksheet.
is there an easier way to do what i am trying to do?
i've tried to give as much information as i can so you can read and look to see what i am doing.

please could anyone sort this out.


worksheet for ahm:


Date Balance Bill Total paid
03/09/06 1000.64 803.54 1804.18 804
10/09/06 1000.18 983.42 1983.60 800
17/09/06 1183.60 787.70 1971.30 800
24/09/06 1171.30 1042.16 2213.46 1042
01/10/06 1171.46 1461.27 2632.73 1460
08/10/06 1172.73 889.70 2062.43 890
15/10/06 1172.43 920.90 2093.33
22/10/06 2093.33 2258.81 4352.14 3000
1352.14


summary sheet:

cust.nam 03/09/06 10/09/06 17/09/06 24/09/06
ahm 803.54 983.42 787.70 (1042.16 =ahm!$C$5)
acc 601.14 1023.01 652.91 774.86
sho 237.28 229.28 229.28 292.32
reh 309.60 316.99 367.05 437.22
emaan 367.77 1385.29 319.97 730.49
hus 266.95 330.24 337.46 335.57
ideal 379.33 494.18 308.06 147.68
amin 545.07 1260.29 708.64 640.36
w.wood 318.26 368.69 390.03 359.64
r r k 404.54 471.99 899.57 633.82
(TOTAL) 4233.48 6863.38 =SUM(E2:E11)

Hi,

assuming that your sheets are named, then

=OFFSET(INDIRECT($A2&"!$A$1"),MATCH(B$1,INDIRECT($A2&"!A:A"),0)-1,2)

as per the attached, note, I didn't think you would appreciate a MS Word attachment for this.

hth

Originally Posted by fasi

Hi,

thanks very much for the solution to my problem. it works just fine.

i was a bit confused of why some customers rows would come up as #REF, but then i worked out that names for the worksheet had spaces or other characters in them.

can you try to explain how the formula you have given works and what the terms in the formula mean.

Thanks again

=OFFSET(INDIRECT($A2&"!$A$1"),MATCH(B$1,INDIRECT($ A2&"!A:A"),0)-1,2)

=OFFSET( << to access a cell from a fixed reference point

INDIRECT($A2&"!$A$1"), << make up from the contents of A2 and the text "!A2&"

MATCH(B$1,INDIRECT($A2&"!A:A"),0)-1,2) << match the contents of B1 with the make-up address column A

Hope this helps.
---