Hello All
Need your help to Show data based from selected parameters in the drop down.
Also, for example, if the selected month is MARCH, it will add up all the month from jan-mar.
Excel file attached.
Thanks.
Hello All
Need your help to Show data based from selected parameters in the drop down.
Also, for example, if the selected month is MARCH, it will add up all the month from jan-mar.
Excel file attached.
Thanks.
Last edited by novena; 02-12-2013 at 01:11 AM.
Hi novena,
welcome to the forum.
Try using below formula:-
Formula:Please Login or Register to view this content.
see attached:-
Dropdown.xlsx
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Why there is a differentiated naming in data like Product# and Brand#? why the text identifier is not unique?
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi and welcome to the forum
try this option.
1. give each month-table a range name that matches the month (January_2013 = A1:K14 etc)
2. give each set of row headings for each month a range name that matches the month, and add a C (January_2013C = A1:A14)
3. On sheet1 change the names from Product1 etc to Brand1 etc....also remove the trailing space on sheet2 in B1
4. them on sheet1 B6, copied down, use this...
=INDEX(INDIRECT(SUBSTITUTE($C$2," ","_")),MATCH(A6,INDIRECT(SUBSTITUTE($C$2," ","_")&"C"),0),MATCH($C$3,SOURCE!$A$1:$K$1,0))
On a side note...it is really important that you use consistency in your headings, else formulas like this 1 will not work
Also, if at all possible, avoid the use of merged cells, thet create havoc with formulas
Hope this helped
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Sixthsense, it should be Product. Have re attached xcel file, Also can we have the sum based on the months selected, for example, if selected March, it will add all from jan-mar.
Thanks
lol gotta love it when they change the goal-posts
hi Dilipandey
Thanks! Formula works, but is it possible to modify and make a sum? For example, if paramters month is March, it will add all jan-mar numbers?
Many thanks,
Appreciate it
Hi Novena,
Suggest you to change the layout of your data, transferring everything in columns like : Month-Year, Brand, Branches (Hotels), Sales and then you can try pivot table.
Regards,
DILIPandey
<click on below * if this helps>
Hi Dilipandey
Thanks for the suggestion, appreciate it. I actually plan to incorporate it to an existing dashboard, Im fairly new to excel so was just wondering if the formula u suggested can be modified to show YTD (based on the month selected) .
Thanks
A suggestion might be to have separate columns for each month (you can hide them if you want), and then have a YTD total, based on those columns?
In B6 cell of Sheet1
Formula:Please Login or Register to view this content.
Drag it down........
In B1 cell of source sheet there is an extra space in Branch1 data.
Please dont ask me to do editing work in the above formula my mind will get collapsed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks