Retrieving quarterly data from sales sheet

monce

New Member
Joined
Apr 17, 2013
Messages
7
All,

Thank you for letting me join your forum. My excel skills are okay in terms of vlookups, pivots and recording macros but i'm struggling with the query below:

I have sales data which is split quarterly into 4 periods. It broken down into product and actual quarterly sales and year to date sales. (year to date sales adds up the actual cumulative sales).


Row/ColumnBCDEFGHIJ
1Actual Quarterly SalesYear to date sales
2Product1
2341234
3A12335235745635437123353584281478251
4B24233523754575843242359468140387246
5C345355645754759765345359098134573144338
5
6
7Quarter3
8
9Actual SalesYear to date sales
10A
11B
12C

<tbody>
</tbody>


My question is that i want to retrieve the actual sales for Qtr 3 and the year to date sales for qtr 3 for each product by selecting the quarter in CELL C7 and bringing back the Quarter 3 data into the relevant product and sales category in cells C10 to D12.

I really hope that this makes sense and that my table helps.

I really appreciate your help.

Monce!
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
do you need to do that just for quarter 3 or generally you want to be able to retrieve this data for every quarter depending on your wish?
 
Upvote 0
do you need to do that just for quarter 3 or generally you want to be able to retrieve this data for every quarter depending on your wish?

Dulitul - thanks for replying. I want to have the ability to change the qtr in cell C7 and bring that that specific quarters data. I want to be able to choose between qtrs 1 to 4.

hope this clarifies.

Thanks for looking at my thread.
 
Upvote 0
guys..... am i allowed to bump my thread? If yes, how often can i do this? I could not see anything in the FAQ section on this.

Plus if anyone can assist with my query above i'd be grateful.
 
Upvote 0
Hey,

Well, I do not know whether this will be of great use to you but I did what you want in a slightly different way. For your products A,B,C(in cells C10:C12) you have to use this formula - =INDEX((C3:C5,D3:D5,E3:E5,F3:F5),0,1,1). In order to use that you need to first select the cells that you want your results to appear (in this case C10:C12) and then instead of pressing Enter you need to press ctrl+shift+enter in order to activate your formula. To change the quarter you need to change the last character in the formula. For example for 3th quarter for products - =INDEX((C3:C5,D3:D5,E3:E5,F3:F5),0,1,3) and for 2nd =INDEX((C3:C5,D3:D5,E3:E5,F3:F5),0,1,2).

You need to do the same for the year of sales. This is the index formula - =INDEX((G3:G5,H3:H5,I3:I5,J3:J5),0,1,3). First select the cells ( in this case D10:D12) change the last character to the corresponding number like the quarter say for the 3th =INDEX((G3:G5,H3:H5,I3:I5,J3:J5),0,1,3) and the press ctrl+shift+enter and the results will appear.

You can always modify the formula further to use different cells. Btw this could be done in VBA but I am not very proficient in writing codes for arrays. For the time being I guess your issue is resolved.
 
Upvote 0
Works great thanks. I actually amended the formula where you were saying to amended to pick up the quarter, I referenced that to a sales period cell so going forward I don't have to mess with the formula.

thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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