Index Match Function that Relates to a Sheet Populated on a Drop Down List

**Fibob** · 02-18-2015, 05:19 AM

Hello everyone!

O, so I'm having a slight issue I can't fix.

I've been given a data sheet that has a drop down at the top. This drop down relates to the week, so when i select a new week, I get a whole new set of data on the same sheet.

What I want to do is take some of that data, and drop it in another sheet. I can do this very easily with:

=INDEX('TRADING REPORT - ALL'!$A$7:$F$134,MATCH('TR - ALL - Period and YTD'!$A4,'TRADING REPORT - ALL'!$A$7:$A$134,0),MATCH('TR - ALL - Period and YTD'!$J$3,'TRADING REPORT - ALL'!$A$7:$F$7,0),MATCH($N$1,'TRADING REPORT - ALL'!$C$3,0))

But everytime I change the week, the index data disappears, because MATCH($N$1,'TRADING REPORT - ALL'!$C$3,0) changes the number - is there a way I can get this to stay, but still change should the raw data change?

Thanks,

Fi

**ELeGault** · 02-18-2015, 05:23 AM

Sort of answer the question in your question.

All you need to do is change the reference to the week number either to a static number on the other sheet you move it to so that row or column is all about that single week or (Not recommended) put in the week Number ie '15' into the formula directly. The index and match is still looking in your raw data, you just need to change the reference to the week number and you should be good to go

Cheers

**Fibob** · 02-18-2015, 06:01 AM

Okay, this is a tad complex for me - this excel stuff is a steep learning curve for me, so how do I go about this?

This part of the function relates to the week match to the dropdown (the dropdown is on the Trading Report - All sheet):
MATCH($N$1,'TRADING REPORT - ALL'!$C$3,0).

How do I modify my function to achieve this?

Thanks,

Fi