Hello, I don't know how to explain this but I will try. I'm using an offset code (=OFFSET(Sheet1!$N$2,COUNT(Sheet1!$N:$N),0,-12,1)) for Dynamic Rolling Chart, meaning the chart displays the last 12 weeks of the column. However, I need to some kind of vlookup code that works together with rolling chart. Meaning, if I have a name x the vlookup would find the last 12 weeks of the name x. Here is the picture and the file.
revise your formula to locate anchor cell for offset.
=OFFSET(INDEX(Sheet1!$N$2:$V$2,MATCH(Sheet1!$A$2,Sheet1!$N$2:$V$2,0)),COUNT(Sheet1!$N:$N),0,-12,1)
If the length of your total sales data can vary by department then you will need to modify the count part of the formula as well
It's working! Thank you so much!
Last edited by AliGW; 04-11-2018 at 07:49 AM. Reason: Unnecessary quotation removed.
What if its a pivot table? Can I use the code like this?
=OFFSET(INDEX(Sheet1!pivottable1,MATCH(Sheet1!$A$2,Sheet1!pivottable1)),COUNT(Sheet1!$N:$N),0,-12,1)
Last edited by AliGW; 04-11-2018 at 07:49 AM. Reason: Unnecessary quotation removed.
you would need to specify the row or column that has the data rather than just the whole pivot table
SovietChild - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks