Hi,
I am working with a list of data on a spreadsheet. From this list I have created another sheet that prompts the user for a specific month and then lists all the data as seen for that particular month.
What I am wondering is - from the spreadsheet attached is there a way I can prompt the user for a month, then along the bottom of the graph have displayed all the "Company Names" with the bars showing either A B C or D the "Category of Non Conformance"
Thanks in advance
G
HoopsahMagic
Hi there,
Does the attached seem anything like what you're looking for? At present it shows ALL results - no month filtering is applied.
Please let me know if you require clarification or further assistance.
Best regards,
Greg M
Hi Greg,
That looks really good.
Let me play with it this morning and I will try and incorporate it into my sheet and let you know the outcome.
Thanks again though, it looks as though I will be able to use it and complete my spreadsheet,
Cheers Greg
G
Hi Greg,
Had a bit of a play with it and can get the chart to work ok.
As I said earlier, I have created a spreadsheet already that updates depending on what month is selected, so when I utilise these formulas in that spreadsheet it works ok.
However, do you know how I can stop duplicate entries. e.g. we may have one supplier who causes an Non Conformance more that once a month, in my spreadsheet the 2 instances are shown as seperates.
Can I have the graph showing a total for these instead of showing it twice
Hi Hoops,
I've had a bit of a play with the workbook as well & you might be interested in the results.
I've started with your original worksheet (totally unmodified) & added two extra sheets, one of which is a graph & the other is graph data.
On the graph data sheet there's space for 40 Non-Conformances. I can add more if you need them.
Also there are two macro buttons "Get Unique Companies List" & "Update Graph". The first one looks through the list of all companies against whom N-C's have been recorded & produces a sorted list with no duplicate names included. This list is then used to populate the "Number of N-C's" part of the worksheet (the blue part). The second button updates the graph to show the number of companies involved for this report.
One minor addition - there's a "Select Month (for chart title)" dropdown list. Selecting the appropriate month will insert the month name into the title of the graph. There's also a "Year" cell (updated manually) which is also incorporated into the graph title.
Hope that these tweaks are of use to you. Please let me know if I can help with any other aspects.
Thanks for the feedback & best regards,
Greg M
Hi Greg,
That works fantastically. I have amended the macros (Slightly) to read from another spreadsheet where I have only a specific months data and by using the Get Company Macro it works perfectly.
However, I am not totally comfortable using a program that I don't really understand. Most of the Macros I can get my head round, but could you explain what is happening in this piece of text:
"j = 3
For i = 1 To 40
If sht.Range("J1").Offset(i, 0).Value <> "-" Then
If InStr(strNames, sht.Range("J1").Offset(i, 0).Value) = 0 Then
strNames = strNames & sht.Range("J1").Offset(i, 0).Value
sht.Range("A" & j).Value = sht.Range("J1").Offset(i, 0).Value
j = j + 1
End If
End If
Next i"
I realise that this is the main body of the program, but could you give me a little pointer to what is happening.
Apart from that, can I just say Thank You once again, as it really does do what I was trying to achieve except mine consisted of about 50 lines and still did not work.
Cheers Greg
Gerry
Hi Gerry,
Delighted to have helped out!
No problem re explaining the code - it works like this:
When "i" has a value of 1, then "sht.Range("J1").Offset(i, 0).Value" points to the first company in the list in column J, i.e. the very first "offender".
As the value of "i" is incremented by the "For" loop the code scans down through the 40 cells in the list. The test:
just ignores the cells which contain only a dash - i.e. the ones at the bottom of the list.
Please Login or Register to view this content.
The variable "j" is used to keep track of the next blank cell in column A - i.e. the list of unique names, & this value is incremented each time a new name is placed in this list.
The only other bit is the one which involves "strNames". This variable starts life with a value equal to the name of the first "offender" in column J, and as the code progresses, other UNIQUE company names are concatenated with it. In our example, by the third iteration "strNames" will contain a concatenation of the full names of Glendinning, Cockburn & Scottish Water. On the fourth iteration the "Instr" function shows that Cockburn (the fourth name on the list) has already been concatenated with "strNames" & so this instance is a duplicate and the name is NOT added to the list in column A.
I think this covers everything, but if you've any further queries please feel free to ask.
Thanks for your feedback & for letting me get involved. This was technically interesting for me & it's nice to know that I've managed to help someone out at the same time.
Hope to hear from you with another "challenge" sometime!
All the best,
Greg
Ok, it all seems a bit clearer now - I work beside a guy who also does a lot in Excel and the 2 of us are pouring over your explanation and your coding and it is starting to come together for us.
Can't thank you enough for your help on this,
Cheers Greg
Gerry
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks