Shums Faruk
asked on
One Userform For Multiple Sheets
Hi Experts,
I have workbook with 12 sheets in it. Each sheet represents months.
I would like to use UserForm ComboBox to display the data of each sheet.
I have created the ProductCode Combo Box & Monthly Labels, but I don't know how to extract data from multiple sheets.
Once the ProductCode selected in Combo Box, I would like to show its monthly sales numbers in front of respective monthly labels.
I would also like to display Fast Moving Item(Maximum number of sale in all 12 sheets) & Slow Moving Item((Minimum number of sale in all 12 sheets) for the year on this userform.
Please help.
Thank you so much in advance
ConsolidatedReport.xlsm
I have workbook with 12 sheets in it. Each sheet represents months.
I would like to use UserForm ComboBox to display the data of each sheet.
I have created the ProductCode Combo Box & Monthly Labels, but I don't know how to extract data from multiple sheets.
Once the ProductCode selected in Combo Box, I would like to show its monthly sales numbers in front of respective monthly labels.
I would also like to display Fast Moving Item(Maximum number of sale in all 12 sheets) & Slow Moving Item((Minimum number of sale in all 12 sheets) for the year on this userform.
Please help.
Thank you so much in advance
ConsolidatedReport.xlsm
ASKER
Hi Norie,
I know that's easiest way to extract data from multiple sheet and sort them in a new sheet.
But I would like to have it in this way? Is it possible?
This UserForm is a part of my previous post...consolidating in one query will loose rewards, so I am trying to get in different post.
I know that's easiest way to extract data from multiple sheet and sort them in a new sheet.
But I would like to have it in this way? Is it possible?
This UserForm is a part of my previous post...consolidating in one query will loose rewards, so I am trying to get in different post.
Add
Public ShtName As Worksheet
To the userform' load the form's combobox with the ShtName's data and then show the form.
Public ShtName As Worksheet
To the userform' load the form's combobox with the ShtName's data and then show the form.
ASKER
Hi All,
I made it bit easier for you all, I assigned monthly worksheets in user form.
I am getting label.caption in all the months, even that selected product code from combobox.list doesn't exist in few months.
Anyway that was just a try to bring the values in label caption, I want sum of Product code against each month's label.
I also added Fast Moving Item TextBox1 & Slow Moving Item TextBox2, for which I want the Product Code number displayed with maximum sale in all 12 sheets.
Hope I may get a positive response.
Thank you in advance.
Good Night All
ConsolidatedReport.xlsm
I made it bit easier for you all, I assigned monthly worksheets in user form.
I am getting label.caption in all the months, even that selected product code from combobox.list doesn't exist in few months.
Anyway that was just a try to bring the values in label caption, I want sum of Product code against each month's label.
I also added Fast Moving Item TextBox1 & Slow Moving Item TextBox2, for which I want the Product Code number displayed with maximum sale in all 12 sheets.
Hope I may get a positive response.
Thank you in advance.
Good Night All
ConsolidatedReport.xlsm
What is it you want exactly?
A summary by month, or a summary by product, or both?
A summary by month, or a summary by product, or both?
ASKER
Good Morning Norie,
I need summary by product.
I need summary by product.
ASKER
Hi Norie,
I found some of your suggestion on below link:
Excel Forum
And so I applied on my requirement, but it does nothing.
Please help.
ConsolidatedReport.xlsm
I found some of your suggestion on below link:
Excel Forum
And so I applied on my requirement, but it does nothing.
Please help.
ConsolidatedReport.xlsm
Replace the code in the userform module with this exact code.
Option Explicit
Dim wbSales As Workbook
Dim wsInv As Worksheet
Private Sub CommandButton1_Click()
Dim x As Integer
For x = 14 To 25
Me.Controls("Label" & x).Caption = ""
Next x
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Set wbSales = ThisWorkbook
Set wsInv = wbSales.Worksheets("Master")
With wsInv
ComboBox1.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
End With
End Sub
Private Sub ComboBox1_Change()
Dim wsMonth As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim idx As Long
Dim I As Long
idx = ComboBox1.ListIndex
If idx = -1 Then Exit Sub ' nothing selected
For I = 1 To 12
Set wsMonth = Worksheets(MonthName(I, True))
With wsMonth
Set rng1 = .Range("C2", .Range("C" & .Rows.Count).End(xlUp))
Set rng2 = rng1.Offset(, 1)
Me.Controls("Label" & I + 13).Caption = Application.SumIf(rng1, ComboBox1.List(idx), rng2)
End With
Next I
End Sub
ASKER
Thanks Norie,
It works perfectly.
I would like to have two more changes:
1. If any product doesn't have its sale for any month, it must show ""(blank) instead of 0.
2. Fast Moving Item & Slow Moving Item yet to display.
It works perfectly.
I would like to have two more changes:
1. If any product doesn't have its sale for any month, it must show ""(blank) instead of 0.
2. Fast Moving Item & Slow Moving Item yet to display.
1) That's easy, we can use the Format function to return a blank when CountIf returns 0 - I'll post that later.
2) I haven't tackled this yet because I'm not sure how you want to determine those values.
Would it simply be the highest/lowest seller over the year?
Or are there other factors to take into consideration?
2) I haven't tackled this yet because I'm not sure how you want to determine those values.
Would it simply be the highest/lowest seller over the year?
Or are there other factors to take into consideration?
ASKER
Hi Norie,
2) Yes, it would be simply the highest/lowest seller over the year, excluding 0 sales.
2) Yes, it would be simply the highest/lowest seller over the year, excluding 0 sales.
Here's the workbook with 1) done, still working on 2) which isn't straightforward.
ConsolidatedReport-V2.xlsm
ConsolidatedReport-V2.xlsm
ASKER
Thanks Norie,
Working Perfect...
Waiting for 2) desperately.
Working Perfect...
Waiting for 2) desperately.
ASKER
Good Day Norie,
Hope you are doing well....
I have few suggestion, I was looking through Consolidate Data Option.
1. We can create a "Report" ws after the last sheet which is Dec, then run a consolidate source Array through Jan till Dec ws.
2. AutoFilter Qnty Column from High to Low
3. Which ever Product Code on B2, will refer as Fast Moving Item.
4. Which ever Product Code in last row of B column, will refer as Slow Moving Item.
5. Then a Label Caption against them will give us the quantity sold.
This would be simplest way, please advice.
I have attached a sample workbook.
ConsolidatedReport-V2.xlsm
Hope you are doing well....
I have few suggestion, I was looking through Consolidate Data Option.
1. We can create a "Report" ws after the last sheet which is Dec, then run a consolidate source Array through Jan till Dec ws.
2. AutoFilter Qnty Column from High to Low
3. Which ever Product Code on B2, will refer as Fast Moving Item.
4. Which ever Product Code in last row of B column, will refer as Slow Moving Item.
5. Then a Label Caption against them will give us the quantity sold.
This would be simplest way, please advice.
I have attached a sample workbook.
ConsolidatedReport-V2.xlsm
The problem is that there are multiple products, 32 in fact that have quantity of 1 over the whole year.
So which one is the slow mover?
I've actually got code to find the fast mover and in the sample date there's only ony one fast mover, but what if there was more than one?
So which one is the slow mover?
I've actually got code to find the fast mover and in the sample date there's only ony one fast mover, but what if there was more than one?
ASKER
Hi Norie,
I would prefer to be first max as Fast Moving and first Min as Slow Moving. We don't need to list all.
I would prefer to be first max as Fast Moving and first Min as Slow Moving. We don't need to list all.
What do you mean by first?
Do you want to take the months into account?
Do you want to take the months into account?
ASKER
first maximum and first minimum in that column.
No, this would be over the year, I don't want monthly basis.
No, this would be over the year, I don't want monthly basis.
That means that the fast/slow movers will always come from January.
ASKER
no Norie,
Fast/Slow numbers will be the sum of items sold over the year.
Fast/Slow numbers will be the sum of items sold over the year.
Sorry but I don't think you are going about this the right way.
Surely if you want to a summary you want to see it product by month not by year.
Surely if you want to a summary you want to see it product by month not by year.
ASKER
Sorry Norie,
For so much confusion, will you able to provide VBA code for Consolidate options as advised earlier, I think we can get that as I m planning.
For so much confusion, will you able to provide VBA code for Consolidate options as advised earlier, I think we can get that as I m planning.
ASKER
Norie,
Check this query;
Sales & Inventory Management System
If you see in this, we can get the result from Sales Column, which ever is highest will be Fast Moving and which ever is just "1" sale will be Slow Moving, if there was more than one, then it must pick the first entry from same column.
Check this query;
Sales & Inventory Management System
If you see in this, we can get the result from Sales Column, which ever is highest will be Fast Moving and which ever is just "1" sale will be Slow Moving, if there was more than one, then it must pick the first entry from same column.
I can consolidate the date from all 12 months, in fact I've already coded that when trying to find the fastest mover.
Though the code didn't actually write the 'results' to a sheet, but that can easily be remedied.
I'll have a look at the latest file you attached.
Though the code didn't actually write the 'results' to a sheet, but that can easily be remedied.
I'll have a look at the latest file you attached.
rewrote all code on this sheet in order to combine with Sales & Inventory Management System
Added slow/fast moving as well.
SalesRegister.xlsm
Added slow/fast moving as well.
SalesRegister.xlsm
ASKER
Thanks again Buttersk,
Two changes I would like to have:
1. I would like to calculate Slow Moving only if its >= 1. Exclude 0 Sales.
2. Slow/Fast Moving Quantity in Label30 & 31.
Two changes I would like to have:
1. I would like to calculate Slow Moving only if its >= 1. Exclude 0 Sales.
2. Slow/Fast Moving Quantity in Label30 & 31.
ASKER
I would think, what if we create a command button for both Fast & Slow Moving Products, after clicks it must display top 10 product with their sales count for fast moving and last 10 products with their sales count for slow moving and it must be >=1.
Just a suggestion. Please advice.
Just a suggestion. Please advice.
your top 10 is perfectly doable... and sounds like a great start to a new question! :)
You would need to outline what you want to do if you don't have enough products for a top 10 or a bottom 10.
You would need to outline what you want to do if you don't have enough products for a top 10 or a bottom 10.
ASKER
:) I still believe, you can surely do it :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Speechless.....Extremely perfect.....
Thanks a million......
Hope to see you soon......I know you don't wish the same :), but I am very glad to work with you.
Thanks a million......
Hope to see you soon......I know you don't wish the same :), but I am very glad to work with you.
Wouldn't it be easier to have a dropdown of months and when a month is selected show a summary of sales for each month.
That summary could be in a 2 column listbox, with the first column for the product code and the second for total quantity sold.