Help with formula: return value from cell in same column but different row if row criteria is met

Lisa F.

Hi, I am trying to figure out how to write a formula to return a value from a DIFFERENT ROW but SAME COLUMN, when a row criteria is met. I can't figure out how to make it work and I am hoping someone can help!

Here is what I am trying to do: in [another sheet], if the date in [row] is [>=Today(+15)] return value in [field] within same row and column.

i.e. if [allocation sheet] has a date in [row 1] that is [within the next 15 days] return the value in [row 2] within [the identified column]

If allocation sheet has a date in row 1 between 15 days and 30 days, return the value in that column in row 2

and so on…

In screenshot: On Sheet "Allocation" if the date in row 1 is [within the next 15 days], return the value in that column (i.e. W09) in row 2 (i.e. 83%) and row 3 (i.e. 31)

Is there a way to do this? Can anyone help me figure this out?

THANK YOU in advance!

Brian_Richardson

Is W09 a date column? It needs to be for this to work. You can put other values in a date column and format them as numbers, but the column must be date.

Alternatively you can convert a text string to a date but it's somewhat painful and you'll need to ensure your dates follow a consistent 2 digit format for months and year.

Like

=IF(DATE(VALUE(RIGHT(INDEX({ALisa's Allocation Projection W08},1), 2)), VALUE(LEFT(INDEX({ALisa's Allocation Projection W08},1), 2)), VALUE(MID(INDEX({ALisa's Allocation Projection W08},1), 4, 2))), <= TODAY(15), INDEX({ALisa's Allocation Projection W08}, 2))

You can see it's getting pretty messy, and if you have to rework this formula for each person and/or each subsequent W column then it's quickly going to get impossible. I'd encourage you to maybe think about rearranging how you capture your data so you could do this using horizontal data in columns per person rather than vertically stacked data. Smartsheet presents like a spreadsheet, but it's more like a database with fields, in that you need to define the types of data in a given column and generally stick with that type.

Brian_Richardson

Hi Lisa you can use INDEX for this. INDEX will take a range of values (like a whole column) and return the nth position in that column. For example =INDEX(range,2) will return the second value of the range.

So, for your formula you can include your various criteria in a nested set of IF statements. Note that the IF statements work in order and stop when they find something that matches, so you have to be specific about the "check order" of the IFs.

I'm assuming you know how to insert a cross-sheet reference in the formula. Below I called it {W09} to refer to the W09 column in your allocation sheet. I also assumed the ">15 days" check was needing the value from a different column over, maybe the next W column? So I called it W10. Obviously, adjust this as needed.

Try:

=IF(INDEX({W09},1)⇐TODAY(15), INDEX({W09},2), IF(INDEX({W09},1)⇐TODAY(30), INDEX({W10},2)))

Lisa F.

Thank you, Brian, I just can't get it to work.

What I want: On Sheet "ALisa's Allocation Projection", if column W08 row 1 is within the next 15 days, return column W08 row 2

What I tried:

=IF(INDEX({ALisa's Allocation Projection W08},1)⇐TODAY(15), INDEX({ALisa's Allocation Projection W08},2)

Paul Newcome

It may be an issue with the way the Community forum's text works. It will convert less than or equal to into a left pointing arrow if you are not careful.

=IF(INDEX({ALisa's Allocation Projection W08},1)⇐TODAY(15), INDEX({ALisa's Allocation Projection W08},2)

=IF(INDEX({ALisa's Allocation Projection W08},1) <= TODAY(15), INDEX({ALisa's Allocation Projection W08},2)

Lisa F.

I wish the answer was that easy…I'm still getting a "#INVALID OPERATION" error.

=IF(INDEX({ALisa's Allocation Projection W08}, 1) <= TODAY(15), INDEX({ALisa's Allocation Projection W08}, 2))

Brian_Richardson

Is W09 a date column? It needs to be for this to work. You can put other values in a date column and format them as numbers, but the column must be date.

Alternatively you can convert a text string to a date but it's somewhat painful and you'll need to ensure your dates follow a consistent 2 digit format for months and year.

Like

=IF(DATE(VALUE(RIGHT(INDEX({ALisa's Allocation Projection W08},1), 2)), VALUE(LEFT(INDEX({ALisa's Allocation Projection W08},1), 2)), VALUE(MID(INDEX({ALisa's Allocation Projection W08},1), 4, 2))), <= TODAY(15), INDEX({ALisa's Allocation Projection W08}, 2))

You can see it's getting pretty messy, and if you have to rework this formula for each person and/or each subsequent W column then it's quickly going to get impossible. I'd encourage you to maybe think about rearranging how you capture your data so you could do this using horizontal data in columns per person rather than vertically stacked data. Smartsheet presents like a spreadsheet, but it's more like a database with fields, in that you need to define the types of data in a given column and generally stick with that type.

Lisa F.

That helped, thank you!

Brian_Richardson

Hooray!