• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using numbers as variables to display labels

MrThorne

New Member
Background:

Hello! I am relatively new to using the more advanced features of excel, and have been staring down this problem for about a day now with multiple failed attempts. I have seen similar problems solved using VBA, but this is not something I have ever done.

I am creating an excel document that management will enter data into daily, and embedded charts will dynamically update. It will keep track of types of problems, their sources,the model being worked on, and who was working that shift. Instead of having multiple charts, they are using the filter feature to only see the information that is most relevant.

Model numbers are alpha-numeric (ES4021E) and there are 107 of them. I defined all of their values and created a drop-down menu to select the appropriate model, and used =VLOOKUP(D2, 'DROP DOWN DATA'!$A$2:$B$108, 2, 0) to assign each model a number so that I could graph it.

The Problem:

While I can chart the number of the model to a particular date, and nest that inside other charts to show what problems occurred on that date and model, I need to be able to label the chart to display the model name and not the number I assigned it. I downloaded the XY Chart Labeling Tool, and could not get it to work for my application.

Constraints:

Whomever is entering in data should be able to select a model, and see it appear on the chart for that day

Solution should avoid VBA if possible

I will need to do something similar with dates employees are working the station as well

I am open to other solutions to the problem
 

Attachments

  • EXCEL HELP.xlsx
    115.9 KB · Views: 5
I played around with this problem a bit more and have it in the format that I was looking for, but for some reason, the labels do not match the data. The chart is from D43 - K54 on the new attachment, and the data it is pulling from is row 1 and 39 from the 'REJECT TALLY SHEET' and column 1 from the 'DROP DOWN DATA' sheet.

Once I can properly label the chart, I will combine it with the other four charts on the first sheet.

Note:

The D43-K54 chart seems to have random dates across the x-axis, even though the proper dates are called out on each point.

The chart does seem to recognize the references to the 'DROP DOWN DATA' sheet, but the values do not match the 2nd row data from the 'REJECT TALLY SHEET'.

Please use the newly posted workbook for reference.
 

Attachments

  • EXCEL HELP.xlsx
    118.3 KB · Views: 1
See attached file

It uses 3 Named Formula for the chart
Date, Model_To and Labels

As an aside your other charts use entire rows for there data
That is really poor practice as it is causing excel to do a lot of work for no return
 

Attachments

  • EXCEL HELP-Hui.xlsx
    118.3 KB · Views: 6
See attached file

It uses 3 Named Formula for the chart
Date, Model_To and Labels

As an aside your other charts use entire rows for there data
That is really poor practice as it is causing excel to do a lot of work for no return

Thank you very much, your solution is perfect, but I don't understand what you did to fix it... How can I set up something like this again?

Also, thank you for the note about using rows for data. I had not fleshed everything out yet, and was originally intending to continue to use this one worksheet for several months worth of data, so I set it so that the chart would continue to look for new input in the row. I think now I may set up a worksheet for each month, limiting how many columns have data, and still be able to compare a year's worth of progress on a dedicated spreadsheet.
 
Examine the 3 Named Formula in the Name Manager
Goto, Formula, Name Manager
 
Back
Top