IFERROR([formula], regurgitate or copy info from another cell in the same row)

Josh Reed

=IFERROR(INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0)), [Total Contract Amount]@row)

The idea is I am cross referencing a PO amendment sheet on my main sheet. If there is a po amendment, I index and match based on the po number. If the index and match fails because there is no amendment listed on the other sheet for the corresponding PO, rather than a message of some kind in "" or a blank space " " I want it to display the data in the column labeled "Total Contract Amount" in the same row.

The reason is for dashboard metrics etc. I need both the final po amount (including amendments) to be in one column, but I don't want the user to have to go into the main sheet and manually adjust contract amounts every time there is an amendment. I feel like this should be easy but I cannot figure it out or find anything. The normal way to regurgitate data from another cell is just ="other cell" but since I'm already using the =IFERROR there's no way to do this.

Am I missing something obvious here?

Thanks!

Josh

Samuel Mueller

@Josh Reed Try:

=IFERROR(INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0)), INDEX({Total Contract Amount}, MATCH([PO Number]@row, {PO number}, 0)))

Instead of "[Total Contract Amount]@row" as the error portion, redo the index match but with the range of Total Contract Amount

Unless I'm confused on where "Total Contract Amount" exists then see below.

Alternate would be:

if(INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0))="", [Total Contract Amount]@row, INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0)))

Josh Reed

@Samuel Mueller thank you for your response. The problem with index and matching again is I need this to populate on the same row. So it would be index and matching the same field with itself. In the actual formula (ignoring the iferror) i am referencing the same PO number in the main sheet with a different sheet that houses only PO amendments. I'll try it but I don't know if that will work, referencing the same @row cell. If that makes sense.

Samuel Mueller

@Josh Reed This formula should work then:

=if(INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0))="", [Total Contract Amount]@row, INDEX({Index NEW po amt}, MATCH([PO Number]@row, {PO number}, 0)))

Samuel Mueller

If there is opportunity for no match then you will need to modify and add an iferror around that formula.

Josh Reed

Ya I think there's no way to do what I want to. What I ended up doing was bringing in the original po amount into the secondary sheet with an index and match, then calculating the difference between the original and the amended po amount (always higher if amended so makes it easier). Then I index and matched again from the original sheet to pull in the difference... THEN I add the original po column to the difference column (pulled in from the other sheet) to get the new total. If there is nothing in the difference column (indicating no PO increase) it simply populates the original PO number (number + 0)

This works fine and I get one column with the actual final PO amount whether or not there is a PO increase, however, because there is so much index and matching... when I enter a new PO increase via the form, it somehow fails to calculate the PO difference in the cross reference... If I look at the secondary sheet the calculation executed fine, but in the original sheet if I look at the reference, within that little preview window, the "original PO" column is blank... meaning in the reference to the other sheet the index and match hadn't happened yet. My gosh that's hard to describe in words... anyway I can have the secondary sheet and the reference manager open at the same time yet the data does not match. Pretty frustrating. I think I'm just trying to get a little too clever...

I should mention one more level of complexity... There is only one intake form and it's on the main sheet. I have automation in place that moves new rows if the form submission is marked as an amendment. It moves them to that secondary sheet. If they select that they are submitting an amendment the form captures the amount and the po number for the index and match stuff to work.

I think I just have too much going on simultaneously and smartsheet goes a little haywire.

Josh Reed

I'm going to try again tomorrow, I can add screenshots since it's gotten so complicated lol

Samuel Mueller

Yeah, send some screenshots tomorrow. It should be able to work, shouldn't need to route data back and forth so much. It's probably a small tweak in the original formula.

Josh Reed

Ok, I've taken some screenshots to better explain the situation. The big hiccup is that we have event and contract hierarchy, then there are sometimes increases to the contracts and they therefore need an amendment. One solution is to just have it appear on this tracker and then the user will have to move the row under the corresponding contract and indent it twice so that it is a child of the contract row. However the clients we are building this for are doctors who have both never used smartsheet before and are not at all interested in spreadsheets. The goal is to have the contract amendments populate the changes on the contract row automatically, then they'll just receive a notification so that they are aware of the change.

First off here is a screenshot of the basic automation... Move the row to a helper sheet if it is marked as an amendment on the intake form.

Next here is a shot of the helper sheet.

Brief explanation as this is hard to read... PO number comes in with the new contract amount via move row automation on the main sheet, then the original is pulled in via index and match on the PO amount from the main sheet. Then it is simply subtracted from the amended amount (always higher) to get the difference... then

the difference is pulled in via index and match on the PO number again and labeled as "Amendment Increase", lastly this is added to the original contract amount to gather the Final amount. I did this solely so that all the final contract amounts are in one column.

It's not that complicated but for whatever reason when a submission is made the difference column is not populating fast enough, or before the index and match on the "mega tracker" tries to pull it in. If I click on the view reference preview dealio it takes me to the proper sheet, is linked to the proper column (difference) but the field is blank. However if I actually go to the sheet it is populated.

I have since kind of deleted stuff and messed with it, but if screenshots of the problem are needed I'm sure I can recreate it.

TLDR: all I need to do is move the amendment submission to another sheet, and then pull in the new amount into the row matching the PO number on the main sheet, so the original contract row. The other thing I need is for rows that don't have amendments to also populate their total in the same column as the ones that have amendments. Perhaps there is an easier or more direct approach I can take.

Thank you so much for your time,

Josh

Samuel Mueller

@Josh Reed I wonder if the "difference" column is the issue. Its almost like you are making a circular reference.

On the mega tracker, create a helper column that brings over the new amendment amount (you can hide this), and then do the difference on the mega tracker instead of trying to pull it from the helper sheet.

Does that make sense?

Josh Reed

Absolutely, I was considering even adding more to the intake form, like the original po amount so that wouldn't need to be brought in to the helper sheet but I like your idea better. Makes sense to keep the helper sheet as simple as possible. I'm thinking the automation is what is happening slower and throwing things out of sync but it's just a hunch. I'll play with that idea and confirm that it solved.

The last piece of it is archiving. We will move rows out of the mega tracker through automation when they are marked complete for a certain amount of time. Smartsheet changes the cell contents to values upon moving so that should be fine, then they would easily know which rows could be deleted on the helper sheet (since it will fill up quickly as well with all the columns) because moving the rows in the mega tracker should break the index and match formulas on the associated rows in the helper sheet.

I appreciate the input and for sticking with me through the lack of concision :)