Change Sheet and cell reference when dragging down formula

Tord1172

New Member
Joined
Dec 30, 2019
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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
 
Thanks for the feedback and good luck!

J.Ty.
Hi Sir. One more clarification on the same topic. What if sheet names are different and its not in any order. For example. Name of first sheet is JAN01 and second Sheet is JAN05, and JAN09 etc... Thanks
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Well, after some googling I have found the solution below. Crucially, the defined name
list_of_sheets is =GET.WORKBOOK(1)&T(NOW()).
Apparently it is a reference to a VBA function (which is always defined and you do not have to create it), so the spreadsheet can be only saved as an xlsm file.
Note that in my case the master worksheet is has number 1, so I must start accessing sheets from 2.

Book1
ABCDEF
1Sheet nameRow of totalTotal valueThree total values directly
2[Tord1172.xlsx]Sheet11017171615
Master
Cell Formulas
RangeFormula
A2A2=INDEX(List_of_sheets,ROW())
B2B2=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2C2=INDEX(INDIRECT(A2&"!C:C"),B2)
D2D2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!C:C"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
E2E2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!D:D"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
F2F2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!E:E"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
 
Last edited:
Upvote 0
Well, after some googling I have found the solution below. Crucially, the defined name
list_of_sheets is =GET.WORKBOOK(1)&T(NOW()).
Apparently it is a reference to a VBA function (which is always defined and you do not have to create it), so the spreadsheet can be only saved as an xlsm file.
Note that in my case the master worksheet is has number 1, so I must start accessing sheets from 2.

Book1
ABCDEF
1Sheet nameRow of totalTotal valueThree total values directly
2[Tord1172.xlsx]Sheet11017171615
Master
Cell Formulas
RangeFormula
A2A2=INDEX(List_of_sheets,ROW())
B2B2=MATCH("Total",INDIRECT(A2&"!A:A"),0)
C2C2=INDEX(INDIRECT(A2&"!C:C"),B2)
D2D2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!C:C"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
E2E2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!D:D"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
F2F2=INDEX(INDIRECT(INDEX(List_of_sheets,ROW())&"!E:E"),MATCH("Total",INDIRECT(INDEX(List_of_sheets,ROW())&"!A:A"),0))
Thanks. Can you please share the new workbook?
 
Upvote 0
Here. It does not work online, you must download it and run on your own machine.

J.Ty.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top