Can't get series to stagger horizontally from specified start date

[bbjm]

Hello everyone,

I was up till the early hours trying to resolve a challenge for which I'm sure there is a (probably obvious!) solution.

Apologies also if this is posted in wrong forum...I am new here. All help is greatly appreciated!

The Objective
I want each series to be staggered horizontally according to its corresponding, specified start date.

More specifically, I want to call into Sheet 1 several series (that I specify in Sheet 1) from an index in Sheet 2, and critically for those series to commence from a start date column (also specified in Sheet 1).

The Problem
Please see image below.
NB the attached excel with this laid out with example formulae I'm using...

The Desired Result
Please see image below in green box.

Thank you in advance.

B

[JohnTopley]

Try

in G29

=IFERROR(IF(G$28<$C29,"",INDEX(Sheet2!$B$2:$K$3,MATCH($E29,Sheet2!$A$2:$A$3,0),COLUMNS($A$1:A$1)-COUNTIF($F29:F29,"")+1)),"")

Copy across and down

And please update your profile with Excel version (Excel 2016, 2019 etc)

[bbjm]

John, thank you very much. That worked a treat!

Very much appreciate you taking the time to write out a solution.

Will try to unpack how it works in due course. The part of the operation not clear to me is ... COLUMNS($A$1:A$1)-COUNTIF($F29:F29,"")+1)),"")

Regards,

B

[JohnTopley]

COLUMNS($A$1:A$1)-COUNTIF($F29:F29,"")+1)),"")

The BLUE is a counter starting at 1 and increasing by 1 as the formula is dragged across cells. The RED calculates the number of blank cells before the first value is placed in a cell. The difference will return a value of 1,2 3 etc which is the index to your data.

so for Sept 22 entry

in K30

=IFERROR(IF(K$28<$C31,"",INDEX(Sheet2!$B$2:$K$3,MATCH($E31,Sheet2!$A$2:$A$3,0),COLUMNS($A$1:E$1)-COUNTIF($F31:J31,"")+1)),"")

the COLUMNS count is 5 and the COUNTIF is 5 so subtracting and then adding 1 gives the first index.

THE COUNTIF starts in column F to avoid a "Circular reference" occurring in Column G (first month) if we had used $Gn:Gn.

Hope this makes it a little clearer!

[bbjm]

That's really great John, again thank you...

Yes this makes sense. I found it helped to break down the formula into separate lines and indent the arguments according to their syntax...more like code. Still took some contemplation!

So, using our method I now can...
- specify arguments in SHEET 1!
- call Series A or B from SHEET 2! into SHEET 1!
- get those series to commence from the specified start dates

Awesome!!

However, I would love to amplify this workflow and apply variable (time dependent) rates to these lists with arguments similarly specified in SHEET 1!

So, for example, applying the arguments I set out in SHEET 1!, I would like to call a list in SHEET 2! and multiply that list with another list in SHEET 3!, and print their product in SHEET 1! at the appropriate point in the timeline.

Boy, have I have tried!!!

Is this possible?


Screen Shot 2022-05-02 at 11.48.10 pm.png

[JohnTopley]

I would create the highlighted table in TABLES and use this in the formula

=IFERROR(IF(G$28<$C29,"",INDEX(Tables!$B$11:$K$12,MATCH($E29,Tables!$A$11:$A$12,0),COLUMNS($A$1:A$1)-COUNTIF($F29:F29,"")+1)),"")