OFFSET formule, then reverse with IF nesting - HELP!

Dear Forum Members,

I would really need your help in solving the below issue. I’ll try to explain as clear as possible.
Please see the attached file. It’s very important that the format of the data should be changed in any way. This is just a sample to give you an idea, but the original file is much more comprehensive then this. This is just to clearly outline the issue I’m facing and needing a solution for.

First:
I would like to copy the data automatically with a formula from Customer List sheet to Subscription tracker sheet. The issue is, that on Customer List the data to be copied is in each consecutive row. E.g. Date in A2, A3, A4, A5, …. However, on Subscription tracker (where the data should be copied from Customer List) should be in every 10th line, e.g. Date in A2, A11, A20, ….

So:

• Customer list A2 -> Subscription list A2
• Customer list A3 -> Subscription list A11
• Customer list A4 -> Subscription list A20

…. Etc.

• Customer list B2 -> Subscription list B2
• Customer list B3 -> Subscription list B11
• Customer list B4 -> Subscription list B20

…. Etc.

• Customer list C2 -> Subscription list C2
• Customer list C3 -> Subscription list C11
• Customer list C4 -> Subscription list C20

…. Etc.

The original file has hundreds of lines, so doing the work manually will not work. I believe the OFFSET or INDEX formulas could be used, but wasn’t able to figure out how

Second:
I would like to copy the data automatically with a formula from Subscription tracker, to Subscription 2019 January, IF the customer signed up for the January issue of the magazine.

So:

IF on Subscription list F2=Yes

• Subscription list A2 -> Subscription 2019 January A2
• Subscription list B2 -> Subscription 2019 January B2
• Subscription list C2 -> Subscription 2019 January C2
• Subscription list D2 -> Subscription 2019 January D2

IF on Subscription list F11=Yes

• Subscription list A11 -> Subscription 2019 January A3
• Subscription list B11 -> Subscription 2019 January B3
• Subscription list C11 -> Subscription 2019 January C3
• Subscription list D11 -> Subscription 2019 January D3

IF on Subscription list F20=Yes

• Subscription list A20 -> Subscription 2019 January A4
• Subscription list B20 -> Subscription 2019 January B4
• Subscription list C20 -> Subscription 2019 January C4
• Subscription list D20 -> Subscription 2019 January D4

….. Etc.

I believe this could be solved with a reverse OFFSET formula with some IF nesting, but can’t figure this out either

Thanks a lot for any help!

Originally Posted by XLent

Try to avoid a) merged cells, and b) OFFSET (as volatile) -- not crucial these days but if your files are big etc can impact general calculation time.

Formula:

'Subscription Tracker'!A2

=IF(MOD(ROWS(A$2:A2)-1,9),"",INDEX('Customer List'!A:A,1+CEILING(ROWS(A$2:A2)/9,1)))

copy across matrix A2:D100



'Subscription 2019 January'!A2:

=IF(A1="","",IFERROR(INDEX('Subscription tracker'!A:A,AGGREGATE(15,6,ROW('Subscription tracker'!$B$2:$B$100)/(TEXT('Subscription tracker'!$E$2:$E$100,"yyyymm")="201901")/('Subscription tracker'!$F$2:$F$100="Yes"),ROWS(A$2:A2))),""))

copied across matrix A2:Dn



'Subscription 2019 January'!E2:

=IF($B2="","",SUBSTITUTE(INDEX('Subscription tracker'!$F:$F,MATCH($B2,'Subscription tracker'!$B:$B,0)+1),"No","Not Subscribed for Feb"))

copied down to En

the Jan tab calcs should be made more dynamic to account for the month -- it would be a good idea to store month of interest in a cell somewhere, and leverage that (to replace +1 and hardwired "Feb" ref. in narrative)

XLent!
Your are really EXCELlent this works perfect
Could I ask for some extra help from you to add a nested IF to the formula you provided for the 'Subscription Tracker'!A2? (the other two formulas are perfect!!)

I attached an updated file. So the new requirement would be that I would like to copy the data automatically with a formula from Customer List sheet to Subscription tracker sheet, only IF that Account Manager is Employee1 (column E on Customer list sheet)

Thanks a million for your help in advance!!!! Much much apprecaited

No need to quote prior posts in full - you'll get into trouble with the Mods.

Given modified requirement you will need to update approach:

Formula:

'Subscription Tracker'!A2:

=IF(MOD(ROWS(A$2:A2)-1,9),"",IFERROR(INDEX('Customer List'!A:A,AGGREGATE(15,6,ROW('Customer List'!$A$2:$A$1001)/('Customer List'!$E$2:$E$1001="Employee1"),1+(COUNTIF($A$1:$A1,"?*")-($A$1<>"")))),""))

copied across matrix A2:D100

The formula works perfect for the sample file, thank you!

However, when I try to adjust it for the actual file, I'm failing grrrr
It is probably, because in the actual file I have different data...

In the SAMPLE file I have Jan-19 in E2 and Yes (or No) in F2, where in REALITY I have MR needed? in E2 and Yes (or No) in F2

[don't ask me why I made this change in the sample.. I guess I just didn't think it matters. It clearly does ]

I attached the updated, and hopefully FINAL sample file. If you could help with this please that would be really really helpful!

PS: noted on not quoting the whole thing. Don't want to mess with the Mods

Sorry, I think it wasn’t clear in my previous reply, that the formula I need to be updated is ‘Subscription 2019 January'!A2

Thanks a lot again for the help!

If I've understood, the only formula that would require update would be:

Formula:

'Subscription 2019 January'!A2

=IF(A1="","",IFERROR(INDEX('Subscription tracker'!A:A,AGGREGATE(15,6,ROW('Subscription tracker'!$B$2:$B$100)/('Subscription tracker'!$E$2:$E$100="MR needed?")/('Subscription tracker'!$F$2:$F$100="Yes"),ROWS(A$2:A2))),""))

applied to matrix A2:Dn