In a second sheet - DATA2018 - I have 3 tables with data for Jan - Dec for each of Sales, Returns & Adj.I also have a table for opening balances.
A1 is a dropdown from a list.For every month there are 2 selections available in the list: (a) data for the period only; (b) data for the YTD
I want to summarise data in my data tables based on the selection made from the dropdown in A1.
Bal b/f if A1=1:simple lookup to the op balance table
Bal b/f if A1=anything else:opening balance + sum of Sales, Returns & Adj for all prev months for all numbers in A3 down
I won't ever have to calculate opening balances for Sales returns or Adj
For the other 3 types of transactions if eg A1=6, then just show the totals for June from the data sheet, but if A1=6-YTD then I need to show the total of Jan-Jun for each
In my data sheet the data is arranged in tables like this:
A
B
C
1
Number
Name
Bal b/f
2
101
Coventry
3
102
Liverpool
4
103
Cardiff
SALES
A
B
C
D
E
F
G
105
Number
Name
Jan-18
Feb-18
Mar-18
Apr-18
May-18
etc
106
101
Coventry
107
102
Liverpool
108
103
Cardiff
Returns start at A205 and Adj at A305
I thought that I could use INDEX-MATCH to find the numbers in A2 etc of my summary sheet, and use SUM+OFFSET to fill out the data, but really not sure what the best way is to do this summary.
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.