Solved

Understanding certain aspects of Dynamic Legends for an Excel Chart

Posted on 2015-01-08
4
76 Views
Last Modified: 2015-02-02
Attached is an Excel file that uses dynamic legends and values for a chart(s). Most of it is understood, but there are a couple questions about the retrieving and formatting of the Legends and some values.  The questions are on the first page of the workbook.
Dynamic-Legends---Questions.xlsm
0
Comment
Question by:Cook09
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
ID: 40538685
Hello,

you are asking these questions in the Excel sheet:

1. How does B12 return only with a numerical value?
2. For Legend Tabs 1-5, how is the width calculated?
3. How is the long color bar generated within each Legend page, above the State?
4. It seems that the Camera feature is being used for the State Legends, but what method changes the picture setting?

Here are my answers:

1. B12 contains the formula =MATCH(Report!$K$3,LegendSheet,0)  The Match function always returns a number. It returns the position of the matched value in the list.

2. The width is calculated with a VBA macro. It is a custom function called RangeWidth() and it uses the range name s.Legend as a parameter. The s.Legend named range exists multiple times, once on each Legend sheet, and contains the range with the mocked up legend on the sheet. I assume that the column widths have been manually set to the optimal fit on each sheet.

3. It's an empty cell with a background fill and the row height is set to 4

4. The legend on the report is a picture. The source for the dynamic picture can be seen in the formula bar when the picture is selected. It is the range name "LegendChoice". The definition of that name is

=CHOOSE(LegendNum,Legend1!s.Legend,Legend2!s.Legend,Legend3!s.Legend,Legend4!s.Legend,Legend5!s.Legend)

So, based on the Legend Number selected in cell B5 on the control sheet, the formula returns the s.Legend range on the respective legend sheet.

The use of the camera tool and the colors look like this comes from ExcelUser.com by Charley Kyd. Is that where you got the file?

cheers, teylyn
0
 

Author Comment

by:Cook09
ID: 40538948
Teylyn,
Yes, this is one of Charley's, I'm on his subscription list...but the explanations behind some of the charts are a little weak.

The reason for question 2, is not being able to find the variable rngCurv. Normally, if a Named Range is selected, the Name of the Range will appear in the Name Box.  But, I can't find where this variable is used within the UDF.  The total is based upon pixels, but how is that calculated? rngCurv has to perform some conversion, or so it would seem.  Would you have some insight on how this is being done?

I saw that the picture was LegendChoice, but is it one picture that changes its values and data based upon a particular Legend? If it's part of the Camera function, then at what point is a variable assigned to show the appropriate data?

Regards,
Cook09
0
 
LVL 50

Expert Comment

by:teylyn
ID: 40539369
I think you are referring to this code:

Function RangeWidth(rngCur As Range) As Single
    ''Calculate this macro each time the workbook is calculated
    Application.Volatile
    
    ''Return the width, in points, for rngCur
    RangeWidth = rngCur.Width
End Function

Open in new window


rngCur is defined as a range variable in the function call. This is how VBA works.

I can create a new function, for example

Function MyTest(myString as String, myNumber as Long, myRange as Range) as string
.... do something
end Function 

Open in new window


At the same time as starting the function, I have declared three different variables. When the function is called, it will require three different parameters: a text string, a number and a range, like =MyTest("Hello",2015,$A$1)
While the function is running, it uses myString, myNumber and myRange as variables that have been populated with the parameters from the function call.

In the spreadsheets, the RangeWidth() function is used in a cell like this:

=RangeWidth(s.Legend)

So the s.Legend range is passed to the RangeWidth function. The statement

    RangeWidth = rngCur.Width

will pass the width in points of that range (which is now represented by the rngCur variable) back to be used as the result of the function.
0
 

Author Comment

by:Cook09
ID: 40571430
teyln...I have tried four times to award a grade (A) and points (500), but it won't move the question as Solved...just abadoned.  Can you help??

Cook09
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now