• 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.

Reference a Fixed Column on Dashboard

L

lightn11

New Member
Hello all,


I've been messing around with the "Simple KPI Dashboard" here on the website, and I think I've reached the point where my vision outpaces my skill.


I have the Financial, Internal Process, and Sales tabs set up so that they ALWAYS reference Column F and Column G (these will be the Current Period and Previous Period, respectively, and the user will add a new column F each update). The idea behind this was to have a running total of each week, but only display the current and previous periods. However, the only way I know how to do this is with =INDIRECT("Data!$F"&ROW(F9)), etc.


I've included the Excel file, if you'd like to take a closer look at it. Please keep in mind, it's an .xlsx file (we're running Excel 2010 at work).

https://rapidshare.com/files/3003720889/NemaKPIs_copy.xlsx


The problem I'm running into is, how can I account for adding a new metric in the Data tab? If the user adds a new row to Data, say row 15, then they have to manually edit each "Current" and "Prior" per metric in the other tabs. Is there a way to automatically account for the change in rows?


Thanks,

Erik
 
You might be able to use offset instead of indirect. Using your spreadsheet, I tried the following formula in cell E15 of the Financial tab:


=OFFSET(Data!$D9,0,2,1,1)


Basically, anchor on column D and offset 2 columns.
 
Hi Erik ,


I am not able to understand your problem. Instead of using the INDIRECT function , why not use VLOOKUP ? Since the metrics in the dashboards have the same text that they have in the data sheet , wouldn't a VLOOKUP do the same job ? It would be immune to any additions on the data sheet as long as the same additions are done on the dashboards.


Narayan
 
I think the problem with vlookup is that it breaks when you add a column. The problem with indirect is that it breaks when you add a row.


I didn't understand at first either, but if you think about the fact that the data expands to the right as new periods are added (at col J), it makes sense. They are only showing the last two periods in the dashboards, so it needs to dynamically adjust as columns are added to the data tab. It does that now, but breaks when a new metric (i.e. row) is added.


An hlookup might work if he changes the dashboards so that Current and Prior equals the actual months using a formula similar to what's in cell K5.
 
Hi ,


Going through the workbook which has been uploaded , I could not find evidence of columns being added or having been added ; since the data only shows current and previous in two columns , I think VLOOKUP should be the easiest option.


Let us wait for Erik to comment.


Narayan
 
No need to wait, I stand corrected. Vlookup does work well, and with the added benefit of dynamic row references, meaning adding metrics to a dashboard is as simple as copying the formula down to the new row.


I tried with the following formula in E15 of a dashboard tab:


=VLOOKUP(B15,Data!C:G,4,FALSE)


I should never underestimate good old vlookup.
 
dmthornton, Narayan,


Thank you so much! It never occurred to me to use a VLOOKUP for this. Classic rookie mistake paired with frustration. Your formula works perfectly! I had tried INDEX, OFFSET, even tried making Data a table, all to no avail. Guess I need to spend even more time on this website and increase my knowledge/skill, eh?


Thanks,

Erik
 
You must log in or register to reply here.
Back
Top