• 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 a named formula in a chart

O

OleMiss2010

Member
I have a named formula that is basically a vlookup. However when I put this named formula into the series values element of a column chart I get an error. Will vlookups not work as named formulas for charts?
 
I believe a VLOOKUP only returns a single value, so it won't work in a chart series. Also, just in case, you also have to include the workbook name when using a named range with a chart.
 
OleMiss2010


As Luke said VLookup only returns a single value


So you can use Vlookup in a named formula if you are charting a single point consisting of an X,Y pair

Use a Named Formula including a VLookup for the X or Y or Both

Chart the Named Formula as normal including the sheet name in the series
 
use Index instead for Charts and named ranges.


for instance

=index((range1,range2,range3),,,Linkedcombobox)
 
You can't use any formula directly in a chart series but you can use Named Formula to define the X & Y values for chart series


You can use any formula including those that generate numbers by calculation or lookup numbers such as VLookup, Index, Indirect, Choose, Offset etc


Here is a simple Scatter Chart where both the X & Y value for a single point are VLookup'ed from a table

https://rapidshare.com/files/964960264/VLookup_Chart.xls


I agree with Montrey that you should try to use non-volatile functions where possible
 
You must log in or register to reply here.
Back
Top