Transpose Formula based on Criteria

[Alcotraz]

Hi everyone,

I am trying to workout the formula ,

Basically, I have a row from A to Z - where I have a formula which returns me TRUE or FALSE ( start of the project) - in below example its in Column X Jan 24.

in the below row, I would like to transpose Project Cashflow values:

I could use =TRANSPOSE('PROJECTED JOBS'!X6:X18) , but I have a number of issues:

1) Transpose function returns SPILL error.
2) I only need to start this function at the start of the project ( so if the above row is TRUE)

Many thanks for any helps


The end result should look like this:

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Alcotraz]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi
Done that , thank you, I am using 2023 Excel ( 365) on Windows Platform.

 

[Fluff]

Thanks for that, clear all formulae from the row & use

=HSTACK(EXPAND("",,XMATCH(TRUE,A2:Z2)-1,""),TOROW('PROJECTED JOBS'!X6:X18))

 

[Alcotraz]

Thanks for that, clear all formulae from the row & use

=HSTACK(EXPAND("",,XMATCH(TRUE,A2:Z2)-1,""),TOROW('PROJECTED JOBS'!X6:X18))

Wow, terrific, thank you so much !

 

[Fluff]

You're welcome & thanks for the feedback.