A formula using the quantities contained within a drop down

laurenceharper

Hi, perhaps a simple one but wondering if anyone can help....?

I have an investment start date column, next to that I have 2 fixed term durations in a multi drop down - the 2 terms are 730 days and 1825 days.

I would like a 3rd column with formula to take the start date and add either 730 days or 1825 days (whichever is selected in the drop down).

Can anyone assist?

Thanks

Laurence

Heather Duff

Hi @laurenceharper ,

Try this, assuming your duration column is named Duration and is a single-select dropdown:

=if([duration]@cell="730 days",[start date]@row + 730, if([duration]@cell="1825 days",[start date]@row + 1825, ""))

Be sure your start date column and the third column (where you put the formula) are both set as date columns within the column properties.

Hope this helps! Let me know if it works.

Best,

Heather

laurenceharper

Hi Heather,

Unfortunately that didnt work - got a #UNPARSEABLE message in the 3rd column once that formula is entered.

Andrée Starå

Hi @laurenceharper

I hope you're well and safe!

Try something like this. (Heather probably confused the @cell and @row, easy to miss)

=
IF(Duration@row = "730 days", [Start Date]@row + 730, 
IF(Duration@row = "1825 days", [Start Date]@row + 1825, ""))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

laurenceharper

Hi Heather

I have actually sorted it now thanks for your time on this one though.

While I have you, do you mind answering something else for me...?

I have a column on a multi-drop down, the categories are as follows:

2022 - Q1

2022 - Q2

2022 - Q3

2022 - Q4

2023 - Q1

2023 - Q2

2023 - Q3

2023 - Q4

2024 - Q1

And so on.

There are 2 formulas I require...

Firstly how do I link these to a range of dates? For example 2022 -Q1 range is within 2/9/21 to 2/12/21, if I enter a date within a date column it will select which quarter that start date falls within a multi drop?

Then secondly, once the first range appears into the multi drop, I would like to drag down an auto fill so the following quarters are sequentially selected?

Many thanks

Laurence

Heather Duff

@Andrée Starå Thank you for the assist! Apparently my brain was scrambled yesterday. :)

@laurenceharper I'm not sure I understand what you mean with the second question regarding drag down and auto fill, but I think I understand your first question.

I would probably create a separate source sheet with 3 columns: Quarter, Start, End. Then enter the start and end dates for each quarter. In your other sheet, then, I'd use INDEX/COLLECT. Here's a good thread on it: https://community.smartsheet.com/discussion/74301/how-to-correctly-use-index-collect

I'll take a stab at the formula, though:

=INDEX(COLLECT({Quarter},{Start},<=Date@row,{End},>=Date@row),1)

{Quarter}, {Start}, and {End} are the named cross-sheet ranges within your source sheet that has the quarter names and dates in it.

Basically, this will make the formula look at the Date column and scan to find which quarter the date falls between the start and end dates of.

laurenceharper

Hi Heather

That didn't work i'm afraid.

I have set up a source sheet see snap shot below:

I have entered the following formula:

=INDEX(COLLECT({{Source Range 7}},{{Source Range 4}},<=Funds Received Date$2,{{Source Range 5}},>=Funds Received Date$2)1)

....but giving a #UNPARSEABLE note.

Below is a view of where we are trying to enter the formula in 'Year & Quarter' and where the date input is 'Funds Received Date'

Heather Duff

@laurenceharper

I see a couple of things. First - do your range names have curly brackets in them? If not, you've got double brackets around them and should only have single. Also, you're missing a comma between the ) and 1. Try this:

=INDEX(COLLECT({Source Range 7},{Source Range 4},<=Funds Received Date$2,{Source Range 5},>=Funds Received Date$2),1)

laurenceharper

Hi Heather, unfortunately that's not working....

Heather Duff

Oh - you need square brackets around your column names that have spaces in them:

=INDEX(COLLECT({Source Range 7},{Source Range 4},<=[Funds Received Date]$2,{Source Range 5},>=[Funds Received Date]$2),1)

Crossing my fingers...

laurenceharper

Now i've got an INVALID note.

I've got this formula in there...

=INDEX(COLLECT({Source Range 7}, {Source Range 4}, <=[Funds Received Date]$2, {Source Range 5}, >=[Funds Received Date]$2), 1)

When I select the source am I supposed to select a range of answers? I.e. for Source Range 7 I am selecting the below:

For Source Range 4 I am selecting all of the Start dates, and Source Range 5 I am selecting all of the End dates?

Heather Duff

You should be selecting the whole column. So you would select the Quarter column, the whole Start column, and the whole End column.

laurenceharper

Should the column be on a specific property setting?

laurenceharper

Hi Heather

Sorry to be a pain...

This is the way the formula is looking - is there something wrong with how I am selecting the source info?

Andrée Starå

@laurenceharper

I'd be happy to take a quick look.

Can you share both sheets with me?

(Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

laurenceharper

Hi Andree

I did share the original with you previously - please check if you can access it

I've also shared the Source sheet as well.