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

Using VBA to show segmented data based on dynamic cell value

T

The Doctor

Member
So I've got a worksheet that uses formulas to project payroll for the year. The problem is that anything more than about a month ahead of right now is really un-reliable. As the pay period approaches the data becomes better. So what I want to do is create a dashboard that only shows the user a segment of data based on today's date. I have a cell using the =today() formula to automatically adjust the date, and a helper column to find the row to start with.


So starting from the row in which today's date lies I need to see 3 rows above and 4 rows below. I have thought about using an index/match and offset formula, but it seems like that could get messy. So I think VBA would work better, I'm just not sure where to begin.


A Sample of the table which holds the data can be found at https://www.dropbox.com/s/czlmv3db7mt41u1/projection%20problem.xlsx


As always, thanks for your help Ninjas
 
Hi, The Doctor!


Why not getting rid of helper column E and define a new dynamic named range based on the actual table Table3, like this?


=DESREF(Table3;COINCIDIR(Sheet1!$D$1-0,5;Table3[Week Starting];1)-4;;9;) -----> in english: =OFFSET(Table3,MATCH(Sheet1!$D$1-0.5,Table3[Week Starting],1)-4,,9,)


It uses 2 payment periods before and 2 after the row with the true condition as per your formula, that's to say a 9 rows range. Please note that you indicated a 8 rows in your worksheet, so adjust either your indication or the range definition.


Regards!
 
Hi ,


Can you check your file here ?


https://www.dropbox.com/s/9jjqrtmt6muv7m6/projection%20problem.xlsx


I have put in the formulae to get the dates , based on some dates prior and some dates after the entered date. You can change the fixed values of 3 and 7 to get the periods prior and after.


Once the dates are available , the other two columns can be filled in using a simple VLOOKUP.


Narayan
 
Hi, The Doctor!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Using%20VBA%20to%20show%20segmented%20data%20based%20on%20dynamic%20cell%20value%20-%20projection%20problem%20%28for%20The%20Doctor%20at%20chandoo.org%29.xlsx


It's your original one with the addition of the range LittleTable3.


Just advise if any issue.


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