Change Sheet and cell reference when dragging down formula

[Tord1172]

Helloo.
I need help with this situation in which I have a summary sheet along with number of worksheets. The sheets are named as DEC1, DEC2 etc.... Every sheet has details of items sold and its sales value for the day. In the summary sheet I want to have details of total sales by each day. I tried using the INDIRECT function. But, the problem is, in every daily sheet, the total sales value is in different cell. For example in DEC1, the sales value is in C55, in DEC2 its C15, in DEC3 its C60. etc... Since the list of items sold very day is different, the total is coming to different cells like that. Is there anyway possible I can use a formula and drag down to get the total sales of each day in the summary sheet? Any help would be appreciated.

Thanks

 

[J.Ty.]

Hi!

Any solution will need a method to identify the summary cell in each worksheet.

- Is it always in column C?
- Is it always in the last row?
- Perhaps there is some fixed text in coumn A of the row with summary?
- etc.

Best,

J.Ty.

 

[Tord1172]

Hi!

Any solution will need a method to identify the summary cell in each worksheet.

- Is it always in column C?
- Is it always in the last row?
- Perhaps there is some fixed text in coumn A of the row with summary?
- etc.

Best,

J.Ty.

Thanks for the reply.. The total is always in column C. And all the totals are in last row. But the row number (row in in which totals are in) will be different in each worksheet depending on the number of items sold. The fixed text in the column A of the row with summary is "Total".

 

[J.Ty.]

The solution is as follows. I made it using "Sheet" as the generic name of a worksheet, you can change it to whatever suits you. Columns A, B and C give the solution step-bystep ,but you can use the formula form column D, which does everything directly.

Please let me know if it solves your problem,

J.Ty.

Book1
	A	B	C	D
1	Sheet name	Row of total	Total value	Total value directly
2	Sheet1	10	12	12
3	Sheet2	14	17	17
4	Sheet3	4	5	5
Master

Cell Formulas
Range	Formula
A2:A4	A2	="Sheet"&ROW()-1
B2:B4	B2	=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2:C4	C2	=INDEX(INDIRECT(A2&"!C:C"),B2)
D2:D4	D2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!C:C"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))

 

[Tord1172]

The solution is as follows. I made it using "Sheet" as the generic name of a worksheet, you can change it to whatever suits you. Columns A, B and C give the solution step-bystep ,but you can use the formula form column D, which does everything directly.

Please let me know if it solves your problem,

J.Ty.

Book1
	A	B	C	D
1	Sheet name	Row of total	Total value	Total value directly
2	Sheet1	10	12	12
3	Sheet2	14	17	17
4	Sheet3	4	5	5
Master

Cell Formulas
Range	Formula
A2:A4	A2	="Sheet"&ROW()-1
B2:B4	B2	=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2:C4	C2	=INDEX(INDIRECT(A2&"!C:C"),B2)
D2:D4	D2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!C:C"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))

Sir, Its not quite what I wanted. Here is some screen shots of what I wanted. In the summary sheet, total from other sheets need to appear automatically when dragging.

 

[J.Ty.]

The totals do appear when dragging, but I made a screenshot of an allready filled spreadhseet.
I believe that it behaves the way you wanted. Just to make sure, please test it here.

J.Ty.

 

[Tord1172]

The totals do appear when dragging, but I made a screenshot of an allready filled spreadhseet.
I believe that it behaves the way you wanted. Just to make sure, please test it here.

J.Ty.

How to bring the totals of all three columns (Gross, Tax, and Total) to summary sheet?

 

[J.Ty.]

Here you are. The file in Excel online has been updated, too.

Book1
	A	B	C	D	E	F
1	Sheet name	Row of total	Total value	Three total values directly
2	Sheet1	10	17	17	16	15
Master

Cell Formulas
Range	Formula
A2	A2	="Sheet"&ROW()-1
B2	B2	=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2	C2	=INDEX(INDIRECT(A2&"!C:C"),B2)
D2	D2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!C:C"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))
E2	E2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!D:D"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))
F2	F2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!E:E"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))

 

[Tord1172]

Here you are. The file in Excel online has been updated, too.

Book1
	A	B	C	D	E	F
1	Sheet name	Row of total	Total value	Three total values directly
2	Sheet1	10	17	17	16	15
Master

Cell Formulas
Range	Formula
A2	A2	="Sheet"&ROW()-1
B2	B2	=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2	C2	=INDEX(INDIRECT(A2&"!C:C"),B2)
D2	D2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!C:C"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))
E2	E2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!D:D"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))
F2	F2	=INDEX(INDIRECT("Sheet"&ROW()-1&"!E:E"),MATCH("Total",INDIRECT("Sheet"&ROW()-1&"!A:A"),0))

Thank you

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.