Breakdown formula not working correctly

Michaela Kamenska

It keeps saying invalid data type for the columns but I can't figure out why:

All dates are formatted as dates too. The Jan-21 columns onwards are only text/number:

Updated slide (the previous slide shows formula from a different cell):

Paul Newcome

Ok. Lets break this down into separate columns a little bit.

First I want to double check this as a column formula in a date type column:

=IF(AND([Global lead]@row <> "EU", [Regional Start-Up Date]@row <> ""), [Regional Start-Up Date]@row, [Lead Start-Up Date]@row)

Michaela Kamenska

Ok and which column would you like me to paste it into?

Lead Start and Regional Start dates are simple index-match cross-sheet formulas now. All formatted as dates.

Lead start:

=IFERROR(INDEX({Lead Start-Up Date}, MATCH([Study #]@row, {study #}, 0)), "")

Regional Start:

=IFERROR(INDEX({Regional Start-Up Date}, MATCH([Study #]@row, {stud #}, 0)), "")

I've actually noticed that this formula works, but again only when EU is the Global Lead:

BUT if I try drag-fill it on the whole row, the [Global Lead] changes to whatever other name column:

Paul Newcome

My apologies. Try that in a temporary testing column. We are going to use this temp column for a series of tests to try to figure out exactly where this issue is coming from.

Michaela Kamenska

Hi Paul,

I've inserted it into a temp column as requested - column also set as Date Type. I was wondering though if this column could be a cross-sheet feeding from a source sheet? The source sheet also has lead and regional startup columns but I was unable to write it so it'd pull correctly, thus I have simple index-match formulas.

Paul Newcome

Are there any rows that are NOT "EU" and the Regional Startup Date is not blank?

Michaela Kamenska

Hi Paul,

in the Source sheet the Global Lead can be either EU, NA or JAPAC. But the regional startup would be left blank if it the regional startup is the same as lead start. So to answer your question - yes, there may be rows where the EU is not the GLobal Lead and Regional Start-Up is not blank. This is the source sheet:

So for example in the first case - NA would start on 22-apr-2018 because they are lead but EU and JAPAC would start on 01-jul-2018. In the third case, EU is the lead so starts 23-oct-19 but NA would start 1-jan-2020. Does that help?

Michaela Kamenska

also on top of what I wrote, in the source sheet the Lead Start-Up date has a column formula =[FPI]@row - 70 -----> FPI is another date my team captures (column currently hidden) and the startup is always approximately 10weeks before that date. (just in case it's relevant).

Paul Newcome

My apologies. I meant there in the target sheet where we currently have that testing column set up. If not are you ablet o enter a sample row so that we can see what happens with our testing formula?

Michaela Kamenska

@Paul Newcome , I've already created a temporary column for the testing.

"Paul Newcome ✭✭✭✭✭ 02/02/22

Are there any rows that are NOT "EU" and the Regional Startup Date is not blank?"

Also, in the target sheet I do indeed have rows where Global Lead is NOT EU and the regional startup is not blank.

If you have a formula I can try test, please let me know. I should have probably name the temp column as "Temporary column for EU Start"

Paul Newcome

I just had a thought... That top row where you have yyyymm entered... Is that manual entry or formula?

Michaela Kamenska

The top row is manual entry all the way. Does that help?

Paul Newcome

This is a long shot but worth a try...

Whenever you combine numbers and text into a single string, you output a text value.

YEAR(TODAY()) + RIGHT("00" + MONTH(TODAY()), 2)

The RIGHT function as well as the "00" both are converting it into text strings, but the manual entry in the top row is numerical values. Now... I know your existing formula is working on some rows, so it doesn't seem like that would be the logical answer, but I want to definitely rule it out if we can.

Wherever you have the bit in the formula that generates the yyyymm, wrap it in a VALUE function.

VALUE(YEAR(TODAY()) + RIGHT("00" + MONTH(TODAY()), 2))

Michaela Kamenska

Hi Paul,

Can you advise which of the below I need to wrap? I am a little confused. This is the current formula that works only on some rows (but still doesn't explain to me why I can't dragfill it on to the row... Please ELI5 what to wrap:

=IF(AND([Global Lead]@row = "EU", [Jan-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Lead Start-Up Date]@row), 2), [Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(AND([Global Lead]@row <> "EU", NOT(ISBLANK([Regional Start-Up Date]@row)), [Jan-21]$1 >= YEAR($[Regional Start-Up Date]@row) + RIGHT("00" + MONTH($[Regional Start-Up Date]@row), 2), [Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(AND([Jan-21]$1 >= YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Lead Start-Up Date]@row), 2), [Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2)), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row)))

Paul Newcome

=IF(AND([Global Lead]@row = "EU", [Jan-21]$1 >= VALUE(YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Lead Start-Up Date]@row), 2)), [Jan-21]$1 <= VALUE(YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(AND([Global Lead]@row <> "EU", NOT(ISBLANK([Regional Start-Up Date]@row)), [Jan-21]$1 >= VALUE(YEAR($[Regional Start-Up Date]@row) + RIGHT("00" + MONTH($[Regional Start-Up Date]@row), 2)), [Jan-21]$1 <= VALUE(YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row, IF(AND([Jan-21]$1 >= VALUE(YEAR($[Lead Start-Up Date]@row) + RIGHT("00" + MONTH($[Lead Start-Up Date]@row), 2)), [Jan-21]$1 <= VALUE(YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row)))

My apologies, but I thin k I might have missed something. I thought we were still struggling to find a formula that worked on every row. I didn't realize dragfilling was the issue. Can you expand on that a bit more? I must have missed that bit.