Help with Formula to Return Value of Another Row when Certain Criteria are Met

Bethany Garcia

I need help with a formula that I have no idea how to do. The sheet columns and an example scenario are below. The column highlighted in yellow is what I'm trying to populate/where the formula will be, showing the values I would expect for each row.

Basically, I want to populate the Reposted Date for a row with the Job Post Date of the row that contains the date of the next reposting. Postings will be recorded in the order shown below in the Orig/Repost column, but won't be nicely ordered one row after the other. Basically, the formula in Reposted Date should look for this:

• IF Orig/Repost = "Original", look for a row where Original Req Number is the same AND Orig/Repost = "Repost (1st)", then return Job Post Date from that row.
• IF Orig/Repost = "Repost (1st)", look for a row where Original Req Number is the same AND Orig/Repost = "Repost (2nd)", then return Job Post Date from that row.
• IF Orig/Repost = "Repost (2nd)", look for a row where Original Req Number is the same AND Orig/Repost = "Repost (3rd)", then return Job Post Date from that row.
• IF no match is found, then Reposted Date should be left blank.

Kelly Moore

Hey Bethany

I updated the formula with your corrected column names. I also added an IFERROR around each Index/Collect.

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

Kelly Moore

Hey @Bethany Garcia

In the first formula we worked, we wanted a date field - I added a criteria to make sure the data had a date in it (ISDATE(@cell)). I see this criteria is still in this second formula. We don't need that now. I removed it from the formula below. It should work now.

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

Let me know if it needs tweaking

Kelly

Kelly Moore

Nope, that's good. In the [Job Post Date] column - are there any errors in the rows above the current row you're working on?

Kelly Moore

@Bethany Garcia

=IF([Orig/Repost]@row="Original", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (1st)"),1), IF([Orig/Repost]@row="Repost (1st)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (2nd)"),1), IF([Orig/Repost]@row="Repost (2nd)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (3rd)"),1))))

To make it easier to read, here's what the formula says. The number "one" at the end of each statement is part of the INDEX function. The Collect function 'collects' data in the range listed- in this case [Job Post Date]:[Job Post Date], based upon the designated criteria.

=IF([Orig/Repost]@row="Original", then

INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (1st)"),1),

IF([Orig/Repost]@row="Repost (1st)", then

INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (2nd)"),1),

IF([Orig/Repost]@row="Repost (2nd)", then

INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (3rd)"),1))))

Does the bold formula above work for you?

Kelly

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/collect

Bethany Garcia

@Kelly Moore thank you, that works! The only thing is that if a row doesn't have another repost row yet (for example, if there is just the Original row and Repost (1st)), the Repost (1st) row is returning #INVALID VALUE instead of remaining blank. I tried adding an IFERROR statement to the formula to display the value if found or a blank if there was an error, but that's giving me an error, too. Any ideas to just display a blank if an #INVALID VALUE is returned?

Kelly Moore

Hey Bethany

Let's try this

=IF([Orig/Repost]@row<>"",IF([Orig/Repost]@row="Original", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (1st)"),1), IF([Orig/Repost]@row="Repost (1st)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (2nd)"),1), IF([Orig/Repost]@row="Repost (2nd)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (3rd)"),1)))))

Does that work?

Kelly

Bethany Garcia

I tried that updated formula and it didn't work, I'm still getting the same result. What if the original formula is nested in an if/then type of statement where we say to return the value if found, or if nothing is found then display blank? That's essentially what's happening I think, that no result is found so it's giving that invalid value error instead of just returning a blank. Or having it return the value if the statement is true, and a blank if false?

Kelly Moore

Hey Bethany

We shouldn't have to insert a blank - when nothing is entered the default action is to do nothing, which is a blank.

I can write the IF with the opposite logic, - this will tell me if the error is occurring because the cell is blank.

=IF([Orig/Repost]@row="", "", IF([Orig/Repost]@row="Original", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (1st)"),1), IF([Orig/Repost]@row="Repost (1st)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (2nd)"),1), IF([Orig/Repost]@row="Repost (2nd)", INDEX(COLLECT([Job Post Date]:[Job Post Date], [Original Req Number]:[Original Req Number], [Original Req Number]@row, [Orig/Repost]:[Orig/Repost], "Repost (3rd)"),1)))))

Bethany Garcia

I was studying it to figure out if anything was different and then did a palm to forehead for not realizing this sooner - may or may not be the cause but if it is then I should have caught this sooner!

The screenshot I provided before was a sample, not the actual sheet I'm using the formula in, so I wonder if that could be a cause. I've been taking what you provided and updated the column headers to match my actual sheet and updated "Original" to be "Original Post" to match my values. Could something in the formula be set to the specific example screenshot I provided and be causing a problem because the actual sheet has columns in a different order? My actual sheet is below with the four columns used in the formula outlined in red.

Kelly Moore

Hey Bethany

I updated the formula with your corrected column names. I also added an IFERROR around each Index/Collect.

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

Bethany Garcia

That one worked!!!!! Thank you so much!!!!!

Bethany Garcia

@Kelly Moore well I thought we were all done, haha. I have another column that I need to do the exact same thing for, same basic logic, but instead of grabbing the Job Post Date and putting it into another date column it's grabbing from the column called "Number of Vacancies", formatted as text, and putting the value into a column called "# Vacancies Reposted", formatted as text as well. I tried just changing the column names in the formula thinking that would do it, but it is just returning blanks. Here is the formula I put in:

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Number of Vacancies]:[Number of Vacancies], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Number of Vacancies]:[Number of Vacancies], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Number of Vacancies]:[Number of Vacancies], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

and a screenshot of the column layout. This is all continued from Original/Reposting from the previous screenshot for reference.

Kelly Moore

Hey @Bethany Garcia

In the first formula we worked, we wanted a date field - I added a criteria to make sure the data had a date in it (ISDATE(@cell)). I see this criteria is still in this second formula. We don't need that now. I removed it from the formula below. It should work now.

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Number of Vacancies]:[Number of Vacancies], [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

Let me know if it needs tweaking

Kelly

Bethany Garcia

That worked, thank you! I appreciate it so much!

Kelly Moore

Anytime. Happy to help

Bethany Garcia

@Kelly Moore I broke the formula! I can't figure out what's going on, but now it's returning a #BLOCKED value. The only change I can think of that I made is that I was previously populating the column Job Post Date with a formula where it equaled the value of another cell, but I realized that was redundant so I deleted that column it was previously populating from and am now just populating the value directly into Job Post Date. I confirmed Job Post Date is a date, and none of the other columns have changed. Here is the formula as it currently is in that column, I haven't made any changes to the formula itself. Does anything jump at you as being the cause? I can give you access to the sheet if that would help.

=IF([Original/Reposting]@row <> "", IF([Original/Reposting]@row = "Original Post", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (1st)"), 1), ""), IF([Original/Reposting]@row = "Repost (1st)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (2nd)"), 1), ""), IF([Original/Reposting]@row = "Repost (2nd)", IFERROR(INDEX(COLLECT([Job Post Date]:[Job Post Date], [Job Post Date]:[Job Post Date], ISDATE(@cell), [Initial Requisition Number]:[Initial Requisition Number], [Initial Requisition Number]@row, [Original/Reposting]:[Original/Reposting], "Repost (3rd)"), 1), "")))))

Kelly Moore

Hey

What formula do you use to populate the [Job Post Date] and do any of the Job Post Date cells have errors in them as a result of the formula?

Bethany Garcia

@Kelly Moore it isn't a formula for Job Post Date to populate anymore, it's an automation workflow to record a date in that field based on a certain trigger. I can provide additional details about the trigger if that would be helpful.