Hello and thanking you in advance. I have attached a DEAD SIMPLE EXAMPLE. It is immediately obvious what needs to be done.
I am struggling with offsetting an array (or at least that is what I think it is).
I have 15 years of deployment (array) which needs to start at some time over the next 15-years beginning at 2013.
=if(d5=2nd Yr (Year Deplyment Starts), then offset (array E5:S5) to a Starting Column defined by the year (in this case the 2nd year))
Year Deployment Starts = a single cell input which determines the year that deployment needs to start.
Array E5:ES = a 15 year array
Starting Column = the point at which the array is offset to.
Thanks in advance
DavidExample Offset Query.xlsx
Put this formula in E4 then copy down and across:
=IF($D4>U$2, "", INDEX($E4:$S4, MATCH(U$2,$E$3:$S$3,0)-$D4+1))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Try pasting
=IF(CELL("col",U6)-$D6 >19,OFFSET(U6,0,-15-$D6),"")
into U6 and copying across.
Martin
Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs.
If my solution has saved you time and/or money, please consider donating to Cancer Research UK.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks