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

Combining =INDEX with =OFFSET

W

Wulluby

Member
Hi,


I have a chart that is working with a scroll bar, in cell W15 I have the following formula:

=INDEX($Q$15:$Q$78,$Z$38)


I am now trying to add the ability to switch from viewing one set of data with another using:

=OFFSET(W15,0,$W$38)


If I am right in how I am interpreting this I need to combine those in the same cell, the closest I’ve got to a result trying to combine these is:

=OFFSET(INDEX($Q$15:$Q$78,$Z$38),W15,0,$W$38)


I take it from my lack of success that this is not the correct way to combine these 2 formulas, would anyone be able to point me in the right direction for this?


To give it some context, I have used the simple gantt chart example as an exercise from http://chandoo.org/wp/2008/05/20/gantt-in-60-seconds-churn-out-an-excel-project-plan-in-no-time/


I have increased the amount of activities to around 50 so that I can learn the basics of setting up a scroll bar which I have got my head round and using the =INDEX($Q$15:$Q$78,$Z$38) to do. At the moment this chart is now displaying from columns titled Planned Start and Planned Duration, I am trying to switch what is being displayed from Planned to Actual. I can see the method behind it and can make it work outside my table with a simple =OFFSET(W15,0,$W$38) but I am not having much luck at combining everything into a chart that will allow me to view either Planned or Actual with the ability to scroll.


I appreciate any pointers anyone might be able to give me on this.
 
I think you just have one extra argument in your formula.

=OFFSET(INDEX($Q$15:$Q$78,$Z$38),0,$W$38)


The INDEX function is being used to return the reference point for the OFFSET, which then looks x columns to the right, where x = value in W38.
 
That works perfect.


It makes a lot of sense too, thanks for that.


I feel like I've learned 1 or 2 big fancy words in a different language but should be mastering the smaller building blocks of sentences like this kind of stuff.


Thanks for your reply Luke.
 
You must log in or register to reply here.
Back
Top