Is there a better way???

Mark.Hendley

=IF($[Year Select]$2 = "2023", [2023]@row, "" + IF($[Year Select]$2 = "2022", [2022]@row, ""))

I am having an issue where I can select 2023 just fine, but when I select 2022 all of my reference dates change to a "Text date" and the formula in the data 1 & 2 columns do not recognize it.

Is there an easy way to fix this?

Or, is there a completely different way I should have this set up?

I only have a couple months of any spreadsheet experience and am open to any and all feedback, Thanks!

Mark.Hendley

@Andrée Starå I have found a solution, it was from a post in 2019 that had a similar but different problem

Instead of adding(+) formulas together, you should nest them.

Comment from @Paul Newcome below

To nest IF statements, you would basically drop the next one into the previous one's "else" section then close out all of the IF's at the end. Eventually it would look something like this...

=IF(this is true, do this, IF(that is true, do that, IF(something is true, do something)))

For me that looks like this...

=IF($[Year Select]$2 = 2023, [2023]@row, IF($[Year Select]$2 = 2022, [2022]@row, IF($[Year Select]$2 = 2021, [2021]@row, IF($[Year Select]$2 = 2020, [2020]@row, IF($[Year Select]$2 = 2019, [2019]@row)))))

Hope this helps others!

bbates

Hi there, ensure all of your column properties match and have the same type of Date.

Mark.Hendley

@bbates They are all date columns and all formatted the same.

Mark.Hendley

@Paul Newcome I see you respond to questions all the time, do you have any input?

Andrée Starå

Hi @Mark.Hendley

I hope you're well and safe!

What happens if you try something like this?

=IF($[Year Select]$2 = 2023, [2023]@row, "" + IF($[Year Select]$2 = 2022, [2022]@row, ""))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Mark.Hendley

@Andrée Starå Unfortunately that didn't seem to work.

It might help to explain my goal, because there may be a different way to do it.

End goal is to have a chart on a dashboard that can change based on the year (or months and other criteria) selected in a dropdown cell on a sheet. I did see some stuff about possibly using a INDEX/MATCH/MATCH formula, would that be an easier way of getting what I want? I cannot wrap my head around how to use that formula.

Have I overcomplicated my work?

Mark.Hendley

@Andrée Starå I have found a solution, it was from a post in 2019 that had a similar but different problem

Instead of adding(+) formulas together, you should nest them.

Comment from @Paul Newcome below

To nest IF statements, you would basically drop the next one into the previous one's "else" section then close out all of the IF's at the end. Eventually it would look something like this...

=IF(this is true, do this, IF(that is true, do that, IF(something is true, do something)))

For me that looks like this...

=IF($[Year Select]$2 = 2023, [2023]@row, IF($[Year Select]$2 = 2022, [2022]@row, IF($[Year Select]$2 = 2021, [2021]@row, IF($[Year Select]$2 = 2020, [2020]@row, IF($[Year Select]$2 = 2019, [2019]@row)))))

Hope this helps others!