This is a guest post by Myles Arnott from Clarity Consultancy Services – UK.
- Part 1: Introduction & overview
- Part 2: Dynamic Charts in the Dashboard
- Part 3: VBA behind the Dynamic Dashboard a simple example
- Part 4: Pulling it all together
In this the final post we are going to pull it all together to create our final Dynamic Dashboard model.
The dynamic dashboard VBA example can be downloaded here [Mirror]
Firstly a quick recap of what we have covered so far:
- How to structure a workbook with user friendly front page and user notes
- Using a structured inputs or driver page
- How to create a range of dynamic charts
- Some simple VBA to move items around a page based on the user’s parameters
Ok, so lets get to work to combine all of the above into a workable model. There is a lot to cover here so as Chandoo often says, may be time to grab a coffee!
First things first you need to bring all of the charts that you want to have in your dashboard into the spreadsheet and label up the tabs accordingly.
Homepage
I find that for a model that has multiple tabs a homepage allows any user to easily understand the contents and easily navigate to the page they are interested in.
Obviously you can make the homepage look entirely how you want. I use this design as my standard layout with a relevant title, business logo and contents structured to suit the model.
I use the free form shape to draw my boxes. This can be a bit fiddly to achieve but I think that the end result is worth the effort.
Links are achieved by inserting hyperlinks. I choose to reformat these to change the color and remove the underlining. [related: create a table of contents sheet in excel]
The help sheet is courtesy of John Walkenbach. Of all of the possible user note solutions I have encountered this to me offers the simplest and most user friendly approach. I use this in all the models that I develop for clients.
Now on to the central point of the model: The Dynamic Dashboard
There are three key parts to the dashboard, the driver area, the report area and the chart location matrix.
Driver area
This is the user interface which allows the user to hide, view or position a particular chart.
Each title is hyperlinked to the page containing the chart in question. Each location cell is a named range with data validation to control the inputs. I have also added an input message via the data validation function.
The named range relates to the chart eg: Chart one is CH_1, chart 2 is CH_2 etc.
The validation list is based on a named range Position_Range (in the Inputs tab)
The input message is defined in the Input message tab within the validation function menu.
Report area
This is your dashboard, the area to contain your charts. It is simply an area defined and formatted to be the container for the dashboard charts that the user chooses.
The Chart Location Matrix
The entries for this matrix are made in the inputs tab and pull through to the dynamic dashboard tab. Each cell of the matrix in the dynamic dashboard tab is a named range. These named ranges are referenced in the VBA. I will discuss this later in the post.
This matrix defines where each chart will be placed. Getting these positioning references correct takes a little bit of trial and error but is pretty simple to achieve.
Now onto the images themselves. The images that the model moves around are pictures of the charts in each tab. This is achieved using the camera tool. Chandoo has written an excellent post on the camera tool so I will not repeat that here.
I have then renamed the images by selecting the image and changing the name in the name box.
As you can see the chart below is called Chart4. You will also notice that in the formula bar it refers to =’CH4′!$B$2:$F$17. This is the image that it has taken from the CH4 tab.
The final step is to add a hyperlink back to the chart page so that the user can navigate to the relevant page by clicking on the image.
So now we have a structured model, images of our charts and everything in place to put in the VBA.
The VBA
The basics for the VBA were covered in part 3 – VBA behind the Dynamic Dashboard a simple example.
Location
Unlike in part 3, as we would like to avoid clicking on a button every time we want to update the layout, the VBA is located in the Dynamic Dashboard sheet itself rather than in a module.
The event target
It important to restrict the event function to specific cells. This stops the macro running completely every time any cell is changed in the sheet. In this case I have restricted it to the cells where the user chooses the chart’s position.
Variables
We need to define the variable in the VBA so that Excel knows where to put the images we have moved.
To do this we firstly define the variables and then link them to the named ranges in the chart location matrix.
To manage the loop code I have also used “I” as the current iteration and “ix” as the last desired iteration.
Moving the charts
This is based on the VBA used in part three of the post.
Loop
To avoid having to rewrite the code for each chart we instead use a loop that continues to loop as long as the current iteration “I” is less than the last desired iteration “ix”. I.e. loop for the eight charts and then stop.
The variable “I” is also passed to the position part of the VBA to select the relevant image to move:
ActiveSheet.Shapes(“chart” & i).Select
Ok So here is the full code:
Dim Topleft_T As Integer
Dim Topleft_L As Integer
Dim MiddleLeft_T As Integer
Dim MiddleLeft_L As Integer
Dim BottomLeft_T As Integer
Dim BottomLeft_L As Integer
Dim Topright_T As Integer
Dim Topright_L As Integer
Dim Middleright_T As Integer
Dim Middleright_L As Integer
Dim Bottomright_T As Integer
Dim Bottomright_L As Integer
Dim Hide_T As Integer
Dim Hide_L As Integer
Dim View_T As Integer
Dim View_L As Integer
Dim i As Integer
Dim ix As Integer
‘Define the position values (based on named ranges)
Topleft_T = Range(“Top_Left_T”).Value
Topleft_L = Range(“Top_Left_L”).Value
MiddleLeft_T = Range(“Middle_left_T”).Value
MiddleLeft_L = Range(“Middle_left_L”).Value
BottomLeft_T = Range(“Bottom_left_T”).Value
BottomLeft_L = Range(“Bottom_left_L”).Value
Topright_T = Range(“Top_right_T”).Value
Topright_L = Range(“Top_right_L”).Value
Middleright_T = Range(“Middle_right_T”).Value
Middleright_L = Range(“Middle_right_L”).Value
Bottomright_T = Range(“Bottom_right_T”).Value
Bottomright_L = Range(“Bottom_right_L”).Value
View_T = Range(“View_T”).Value
View_L = Range(“View_L”).Value
Hide_T = Range(“Hide_T”).Value
Hide_L = Range(“Hide_L”).Value
‘Set ix to be the number of charts
ix = 8
‘reset i
i = 1
”Select the shape and position it
Do While i <= ix
ActiveSheet.Shapes(“chart” & i).Select
If UCase(Range(“CH_” & i).Value) = “TOP LEFT” Then
Selection.ShapeRange.Top = Topleft_T
Selection.ShapeRange.Left = Topleft_L
ElseIf UCase(Range(“CH_” & i).Value) = “MIDDLE LEFT” Then
Selection.ShapeRange.Top = MiddleLeft_T
Selection.ShapeRange.Left = MiddleLeft_L
ElseIf UCase(Range(“CH_” & i).Value) = “BOTTOM LEFT” Then
Selection.ShapeRange.Top = BottomLeft_T
Selection.ShapeRange.Left = BottomLeft_L
ElseIf UCase(Range(“CH_” & i).Value) = “TOP RIGHT” Then
Selection.ShapeRange.Top = Topright_T
Selection.ShapeRange.Left = Topright_L
ElseIf UCase(Range(“CH_” & i).Value) = “MIDDLE RIGHT” Then
Selection.ShapeRange.Top = Middleright_T
Selection.ShapeRange.Left = Middleright_L
ElseIf UCase(Range(“CH_” & i).Value) = “BOTTOM RIGHT” Then
Selection.ShapeRange.Top = Bottomright_T
Selection.ShapeRange.Left = Bottomright_L
ElseIf UCase(Range(“CH_” & i).Value) = “VIEW” Then
Selection.ShapeRange.Top = View_T
Selection.ShapeRange.Left = View_L
ElseIf UCase(Range(“CH_” & i).Value) = “HIDE” Then
Selection.ShapeRange.Top = Hide_T
Selection.ShapeRange.Left = Hide_L
End If
i = i + 1
Loop
End If
End Sub
Closing Thoughts
We now have a model that provides pertinent summary information to aid management decision making. It combines a high level of flexibility within each report and then allows the user to choose which reports to include and where to position them. This allows an enormous amount of flexibility over the message to be communicated.
I hope you enjoyed the post and please feel free to make comments and suggestions on the model.
Finally a huge thank you for Chandoo for agreeing to host this post for me.
Download the complete dashboard
Go ahead and download the dashboard excel file. The dynamic dashboard can be downloaded here [mirror, ZIP Version]
It works on Excel 2007 and above. You need to enable macros and links to make it work.
Added by Chandoo:
Myles has taken various important concepts like Microcharts, form controls, macros, camera snapshot, formulas etc and combined all these to create a truly outstanding dashboard. I am honored to feature his ideas and implementation here on PHD. I have learned several valuable tricks while exploring his dashboard. I am sure you would too.
If you like this tutorial please say thanks to Myles.
54 Responses to “6 Tips for Writing Better VLOOKUPs”
Hi, I am loving the VLOOKUP series this week. 🙂
Could you please expand a little on why you don't recommend using 1 or 0 in place of true or false? I am in the habit of doing this.
"You can even omit the last argument if it is 0"
Excel's default for the last argument is TRUE. Because of this, it's dangerous to omit the last arguement. I would use either FALSE or 0. Never omit if you want an exact match.
Nice series, Chandoo!
.
Your readers may be interested to know that the quickest formula method to do lookups in Excel is an array-entered INDEX.
.
This is one of the many topics covered in the Excel Hero Academy:
Excel Hero Academy
.
Regards,
Daniel Ferry
Excel Hero Academy
Dear Daniel,
I had used index-match with absolute reference for the ranges but when I am resorting the table the formula is not recalulating the lookup value combination.
Regards,
Anish Menacherry
@Anish
Can you post the question at the Chandoo.org Forums
http://chandoo.org/forum/
Please include a sample file so we can review the issue
1. Never use VLOOKUP/HLOOKUP - Always use Match /Index
2. Sort your data before performing a Loookup
3. Use 1/-1 option Match as it is at least 10 times faster than the 0 option- But modified to perform an exact match rather than an approximate match as described below
a) A Column containing a Match Fucntion to Find the Position with the 1/-1 option
b) A Status column containing a Index to check the status (present/not present)
c) Multiple array entered Index colums to pick
In tip number 5 you state, "you can even omit the last argument if it is 0" which is not correct. If you omit the last argument, Range_Lookup, is TRUE, as Mike Alexander points out.
Excellent series - Need some help from the expert. how easy it is to add/expand a named range in a lookup formula?
@Mike & Gregory: I am sorry for the confusion. The formula =VLOOKUP(value, range, column #) assumes last argument as TRUE.
Where as the formula =VLOOKUP(value, range, column #, ) assumes last argument is blank or empty which internally gets treated as 0.
And that is what I mean by you can even omit last argument. I state that "Remember, you must place a comma (,) after the column number if you are planning to use this." otherwise, this will not work.
@Andrew: I suggest not using 0 or 1 as they are more cryptic and lead to confusion when your spreadsheet gets to someone else's hands.
@Daniel: Thanks for that.
@Sam: Good tips. I would just add that using VLOOKUP / HLOOKUP is ok as long as they solve the problem you have and do not take too much time. The performance improvements you get with array entered index or other techniques are minimal when dealing with small and moderately sized data sets.
@Sundeep
Very easy
Have a read of: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
Particularly Point 3. Create a new named range and type OFFSET formula
@Hui - Thanks.
If I have a large workbook with many Vlookups and if I change the range to named range...is there an easy way to change all the formulas? It is more of wishful thinking than a question 🙂
@Sundeep... You can use Apply names from formulas ribbon to apply names to a selected range. This technique works when the ranges are mapped to static references. Dynamic refs. thru OFFSET are bit more tricky.
You can use the find / replace to automatically replace all $A$1:$C$1000 with dynamic range lstData. See this: http://chandoo.org/wp/2009/02/17/spreadsheet-formulas-edit/
@Sundeep
On the Formulas Tab, Click on the Drop Down on the Define Name button and select Apply Names
Select one or all Named Ranges and apply
Excel will go through your worksheet/s and change the Ranges for Named Ranges.
i cannot believe i missed the new to 2007 formula "IFERROR". your mention of this will help reduce the number of characters in many formulars i use (with "ISERROR") by at least 40% along with commensurate reductions in spreadsheet size and calculation speed... not to mention future reduction in typing and debugging time in formulas. thank you. and thank excel.
Newbie here.
I am not able to understand the Tip#1. Use of "val", "tbl". I tried and it kept on giving error.
Chandoo's Tip#1: =VLOOKUP(valSalesPerson,tblData,3,FALSE)
Does it need column headings? And how do you l lookup the value I am looking.
Thanks in advance.
[...] 6 VLOOKUP Tips [...]
[...] VLOOKUP, INDEX, and MATCH: Useful for looking up any text values [...]
I need some help with creating a formula. I have a list of names on tab 1. (About 20) On tab 2 I have a list of names and there total sales (About 3,500) I created a name range for both the first list of names on tab 1 (Producer) and a name range for the second list on tab 2 (Agent_List) The sales on tab 2 for each producer is in the 7th colume.
I need the formula to identify name of Producer (Tab1) from the Agent_List and then choose the total sales for that producer.
This is the formula I put together and I only get #REF!
VLOOKUP(PRODUCER,AGENT_LIST,7,FALSE)
@JimH
I assume you are adding a column next to the Agent_List on Tab 2 and looking up values from the Agent_List and retrieving values from the Producer list
.
So the format for your equation will be:
=VLOOKUP(A2,Producer,7,FALSE)
or
=VLOOKUP(Agent_List,Producer,7,FALSE)
.
Note that the named range Producer must be at least 7 columns wide, not just Column A or you will get the #REF! error also
Hi
Can anyone please help or this totally impossible in excel? I am trying to do a vlookup with a range of cells that contains "comments" in them and unsuccessful.
Thank you
@Lala
You cannot search within comments unless you use VBA
My tips are:
Pay attention to data types - no fly if mixing text and numbers. I run into this problem a lot with files downloaded from access that have a tendency to mix data types on me when it hits excel.
Pay attention to $ - If pulling from the same workbook, $ won't auto fill on your range and you will potentially miss hits.
Yeah, the data type mixing has bitten several folks I work with in the rear.
EG: I work at a company where marketing source codes are 10-alphanumeric. But, some codes are like "12345" while others are "123abc". When access or sql dumps to excel, the numerical ones convert to numbers while the text ones stay text.
So, what I do is create a reference column next to them in which I do a =TRIM([column]). Trim not only removes front/back spaces, it converts a value to text data type. This is useful, b/c sometimes sql db admins will store data with a fixed string length (eg: a column may get stored as char(50), which means it will have 50 chars no matter if it has to add extra spaces at the end to pad it out.) When you dump this to excel, the extra spaces remain at the end. So, the Trim command not only converts numbers to text, it removes padded spaces at the end. Very useful when working with sql dumps.
I have two sheets, in first sheet i have given a criteria of month (like jan, feb), then on another sheet i have month wise sheet like
jan feb mar
a 2 5 8
b 5 9 8
c 9 12 89
now i need in first sheet if i give criteria jan then answer is 2+5+9, or if i give feb then answer is 5+9+12 and like that, how to get that??
I am pretty well versed in VLOOKUP but I have a challenge I can't figure out. When I complete the VLOOKUP in one cell, it works fine. When I drag the formula down (using $ where necessary) the value from the first LOOKUP populates in the new cell. If I double click on the cell and hit 'enter' then the correct value is pulled in from the vlookup. Any suggestions why the formula isn't executing correctly until I hit enter?
@Nicole
It sounds like Calculation is set to Manual
Goto the Data Tab Calculation and set it to Automatic
Absolutely FANTASTIC!! Thank you so much. Slight variation on my version of Excel. I had to go to Formulas Tab then to Calculation sub-tab, Calculation Options, change setting to Automatic. Thank you thank you thank you. Saved me hours of more frustration!
[...] than maybe sorted, which it usually is anyway).Use COUNTIF or MATCH to speed up calculationAs many others have pointed out, VLOOKUP returns #N/A if the lookup value is not found. Instead of using a [...]
I have more than 2 columns in a table I'm so confused cuz the results i get is #N/A =(
I have a 2-sheet database. Sheet 2 has a list of Accronyms in column A and their description in column B. On sheet 1, column A is where you input your Acronym. In column B, the formula takes Acronym from column A, looks it up on sheet 2, and displays it on column B.
After some research, I found how to make custom text if there is not a match on the Acromyn. The question i have is, is that when there is no text in comumn A, sheet 1, column B, sheet 1 displays my custom text "ABBREVIATION NOT FOUND". I'm trying to write a forumla that leaves column B blank unitl there is an input in column A.
This is my current forulma:
=IF(ISNA(VLOOKUP(A4,Description!A:B,2,FALSE)),"ABBREVIATION NOT FOUND",(VLOOKUP(A4,Description!A:B,2,FALSE)))
Any help out there?
Thanks,
Jerome
Hi Jerome... Thanks for your question. Try this formula instead:
=IF(A4<>"", IFERROR(VLOOKUP(A4,Description!A:B,2,FALSE),”ABBREVIATION NOT FOUND”), "")
Works in XL 2007 or above. For older versions use this:
=IF(A4<>"", IF(ISNA(VLOOKUP(A4,Description!A:B,2,FALSE)),”ABBREVIATION NOT FOUND”,(VLOOKUP(A4,Description!A:B,2,FALSE))), "")
Btw, to learn more about IFERROR see this: http://chandoo.org/wp/2011/03/11/iferror-formula/
I have 2 worksheet, the first one is like this:
A B C D
1 DOG 1 BROWN
1 DOG 2 WHITE
2 CAT 1 SMALL
2 CAT 2 MEDIUM
2 CAT 3 BIG
THE SECOND WORKSHEET IS LIKE THIS:
A B C D
ENTER# fORMULA 1 WITH VLOOK ENTER # FORMULA 2
(RETURN ANIMAL) RETURN TYPE
FOR EXAMPLE i NEED WORKS LIKE THIS:
2 CAT 2 MEDIUM
FIRST FORMULA IS EASY NOT PROBLEM. bUT FOR THE SECOND i DO NOT FIND HOW TO DO IT. PLEASE HELP.
This would be how I would handle your second formula, in your first worksheet, I would insert a column between C & D. In that column I would have a formula to concatenate the values in column A & C (example =concatenate(a2,c2)) which would result in:
A B C D E
1 DOG 1 11 BROWN
1 DOG 2 12 WHITE
2 CAT 1 21 SMALL
2 CAT 2 22 MEDIUM
2 CAT 3 23 BIG
Then in the second worksheet formula 2 would be:
=vlookup(concatenate($a2,$c2),AnimalType columns D&E,2,false)
Great Stuff Chandoo
In your 6th post you say use SUMIF instead of VLOOKUP as it runs faster.
What if you have a spread sheet with repeated data and you only want to pull one value back?
would it be best to use a simple VLOOKUP
or something like: IF(COUNTIF < 2, SUMIF, VLOOKUP)
I have set COUNTIF < 2 (not just = 1) to take advantage of the fact that if COUNTIF = 0 you won’t get an error
Now if only you could use the column header name instead of the column index number in the VLOOKUP function.
Scenario: I have a list/table in one spreadsheet that I use to lookup values in other spreadsheets. If I insert columns in my list/table, I have to go into the other spreadsheet(s) and increment the VLOOKUP formulas' column index number to capture the right column of values.
Example: if I inserted a column in Table1, my formula:
=VLOOKUP(A1,Table1,2,FALSE) would have to change to:
=VLOOKUP(A1,Table1,3,FALSE),
it would be so much better if you could code something like:
=VLOOKUP(A1,Table1,Table1[price],FALSE)
If my lookup result is numeric data I could use sumif as suggested and use the list/table references; is there a similar function I can use for alphanumeric data lookups that uses list/table references?
[…] Read more – 6 VLOOKUP tips […]
tip:
you can use dynamic column reference for your look up if you want to pull multiple column values from another sheet with the same row reference without having to rewrite the the formula, e.g.
range a1:d1 = "header", 2 , 3, 4
b2 = vlookup($a2, LookUpRange, b$2, 0)
c2 = vlookup($a2, LookUpRange, c$2, 0)
b3 = vlookup($a3, LookUpRange, b$2, 0)
the above will bring back the value two columns away from LookUpRange in b2, 3 for c2 and 4 for d2 for the same reference, a2. By freezing just the column for your lookup reference value and just the rows for your column reference, you can drag your forums both down and right while keeping all reference both constant and dynamic... as oxymoronic as that sounds.
my TIP, building on what Andy says above re using a dynamic refrence: if you use the column functon in the header row - should someone add extra columns to the source sheet your lookup will adapt and still return the right result.
With the below formula I am getting "too many arguments for this function. any help?
=IFERROR(VLOOKUP(RIGHT(M3,7),notes!A:A,1,FALSE),"Failure to process correctly",IFERROR(VLOOKUP(RIGHT(n,2),notes!A:A,1,FALSE),"Failure to process correctly"))
Chaz - IFERROR only requires 2 arguments, you have entered 3 (the vlookup, the error message, the 2nd IFERROR).
Change your formula to the following:
=IF(isERROR(VLOOKUP(RIGHT(M3,7),notes!A:A,1,FALSE)),”Failure to process correctly”,IFERROR(VLOOKUP(RIGHT(n,2),notes!A:A,1,FALSE),”Failure to process correctly”))
Ian
Hmm, I'm not sure my formula will return the required output.
This tests if there is an error in the 1st vlookup, then checks the 2nd, and only returns the error message if both vlookups are errors. Is that what you wanted to do?
=IF(isERROR(VLOOKUP(RIGHT(M3,7),notes!A:A,1,FALSE)),IFERROR(VLOOKUP(RIGHT(n,2),notes!A:A,1,FALSE),”Failure to process correctly”),VLOOKUP(RIGHT(M3,7),notes!A:A,1,FALSE))
I am trying to use a vlookup with a named range for the lookup array. This works fine. However now I would like to replace this named range with a cell reference (which obviously contains the name of the named range) but get a N/A error message. Is this really not possible?
vlookup ( A1, named range, 2, 0 ) . This works
vlookup ( A1, F1, 2, 0 ) . Where cell F1 contains the the text with named range. This does not work.
Any tips or thoughts would be appreciated. Thank you in advance
@Erik
Use: vlookup ( A1, Indirect(F1), 2, 0 )
Works like a charm. Thank you!
Some opinions on the pros and cons of using named ranges on http://www.excelvlookuphelp.com along with a few other hot tips
Hello,
Chandoo,
Can u explain me how to use vlookup formula in 2 sheets in one excel workbook.
Hi am Using Index match function to overcome the limitation of Vlookup. But I am failed to get the same result as i get in Vlookup. in vlookup as we can expand the Columns of Vlookup in one single shot. Like Vlookup($A4,A1:G9,3,0) but same Result i Not get in Index match Function. Please help
@Satish
I will suggest that your list is unsorted and it is possible that VLookup is returning a wrong answer
Can you post a question at the Chandoo.org Forums
http://chandoo.org/forum/
Post a sample file and someone will review
I want to upload a Sample file Contain my Question. but i can't see and upload file button on the page. Please Tell how to upload the File
@Satish
You can't upload a file here
But you can on the Forums
Goto:
http://chandoo.org/forum/
Select a Forum
Start a New Thread
Upload a File, is at the Bottom next to the Post Button
Refer: http://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705
thanxx... Soon i will Upload It.
Dear Excel super-users,
Sourcing data from different sheets.
I'd like to specify in the vlookup formula which sheet to source data from.
This source sheet will change depending of the name of the person selected in a specific cell C1 on the sheet where the vlookup formula is being run from.
I'd be grateful for any tips to achieve this.
Regards,
Sean
dear sir /madam
please proved me lookup formula
and exp--------- insert picture formula attched excel sheet
Us the Column formula in place of the 3rd argument will save you time when you want to bring in all data columns!