Please refer to the picture. I would like to have one formula in the red marked cell (I4) which I can drag-down. I tried several formulas (like ADDRESS, INDIRECT or OFFSET) to kind of work with an array.
I tried the follwoing so far:
In cell I4 I made just the reference to the client name:
=Sheet1!A4
In cell I5 I started working on the formula:
=IF(Sheet1!D4-COUNTA($I$4:I4)>0;I4;ADDRESS(4+(Sheet1!D4-COUNTA($I$4:I4));1;1;FALSE;"Sheet1"))
Your help is much appreciated.
Can be easily achieved with a few helper columns, INDEX and MATCH formulas.
First helper, is a 'First Row' column, in column E (next to your Sum column). In cell E4 add =D4+N(E3) and drag this down for all your Client/Service rows
Then on Sheet2, add a 2nd helper column (Client Row) between your Row column and your Client columns. If Row 1 is in cell A4 on Sheet2, then put =IFERROR(MATCH(A4-1,Sheet1!$E$4:$E$6,1)+1,1) in cell B4, and then drag down.
For your Client column, in cell C4 put =INDEX(Sheet1!$A$4:$A$6,B4) and drag down.
Then when you change your values in your service columns the data on Sheet2 will update. I've assumed that if a client has zero services on Sheet1 you don't want them to appear on Sheet2, so for example if you make Client B 0 for service1 and service2 the your Sheet2 list will just show Client A and C, but with errors after the last client C row. Some error handling can be built into the formulas to deal with this if you want.
There should be other/easier ways to do it. But this is my approach.
Suggestion: Solve this kind of problems by breaking them in smaller steps.
Setup:
Formulas per column:
How many times (previous cell): =IFNA(VLOOKUP(H3;$A$4:$B$6;2;FALSE);0)
Count appearances in result (previous cell): =COUNTIF(H$3:H3;H3)+1
Find changes: =IF(E4-D4>0;1;0)
Sum changes: =SUM(F$3:F3)
Result: =IF(IF(F4=1;OFFSET($A$4;G4;0);H3)<>0;IF(F4=1;OFFSET($A$4;G4;0);H3);"")
Drag the formulas down
