using a INDEX/LOOKUP function to retrieve data on another sheet

[sunshineKel]

Hi!

(LOVE the site by the way...)

So, I have a KPI document, with data over weeks (so week numbers then a range of kpi's which are entered by the team each week). Then I have lots of sheets with different products' kpi's over weeks.

I would like a summary sheet (which will become my dashboard when I've completed your tutorials) with a Week Index number and a summary table. In the summary table, when you've entered the relevant week index number, it looks up that weeks' corresponding data in the relevant sheet. Change the week number and the data changes.

This is probably so simple, and I can see as I write I need to use INDEX and (probably) VLOOKUP functions, and I've even done it before a long time ago, but I'm drawing a huge blank now.


Your input would be just lovely. And will save me a grey hair or two.


Lots of admiration and awe,

Kel

 

[dan_l]

Yup. If your data is already prepared sitting on another sheet, you can use index or vlookup as your driver.


Here's a protip: if this is a 'living document' kind of project where people will be adding data every week or month, use dynamic named ranges. They're really easy. That way, you can have your vlookups point to a range that's automatically expanding and saving you from having to rewrite the formula every time.


So instead of:

=vlookup([user input], ProductADataSheet!$a$1:$f$100,blah,FALSE) you can just put it in as =vlookup([userinput,ProducAData,blah,false). It will most definitely save you grey hair.

 

[sunshineKel]

Thanks dan! I think I got it to work. Nice protip too.