Help with a potentially complicated formula.

Delilah

Looking for a formula to do the following with these two sheets. Thank you

Monique Odom-Stearn

Hello @Delilah!

I think something like this would work:

=IF(INDEX(COLLECT({Grant Fund},{Procurement ID},[Procurement ID]@row),1)="EDA SMART MEMS and ODHE Super Rapids",[Total Cost]@row/2)

The Index Collect part of this formula is collecting the information in the Grant Fund column from the row that matches the Procurement ID. The IF formula is asking: "If the information pulled = "text", then divide the information in the Total Cost column by 2.

Kelly Moore

Hey @Delilah

Try this. You will need to physically create your own references between sheets - you cannot simply copy paste the formula

=IF(COUNTIFS({sheet 2 Procurement ID},[Procurement ID]@row, {sheet 2 Grant Fund}, "EDA SMART MEMS and ODHE Super Rapids")>0, IFERROR(VALUE(INDEX(COLLECT({sheet 2 Total},{sheet 2 Procurement ID},[Procurement ID]@row, {sheet 2 Grant Fund}, "EDA SMART MEMS and ODHE Super Rapids"),1))/2,"")

Does this work for you?
Kelly

Delilah

Hi,

Thank you Kelly. I am confused by your answer a bit. Why is it referencing the Total on Sheet 2? I need to divide the Total Cost on Sheet 1 and enter that amount into the Total column on sheet 1 based on the Grant Fund on sheet 2.

Paul Newcome

Give this a try:

=IF(COUNTIFS({sheet 2 Procurement ID},[Procurement ID]@row, {sheet 2 Grant Fund}, "EDA SMART MEMS and ODHE Super Rapids") > 0, [Total Cost]@row / 2)

Delilah

Hello,

Thanks for this. I tried putting it in and it says incorrect argument. I tried tweaking it a bit, but still not working.

Delilah

Does MATCH or INDEX need to be included somewhere?

Paul Newcome

If you are getting that error, double check your ranges are set correctly.

Delilah

I redid it and it says calculating which I remember it said calculating the very first time I entered it, but it doesn't return anything as though its not a match, but actually it is and should provide a total that is half of the total cost. That's why I tried changing it. Any thoughts?

Monique Odom-Stearn

Hello @Delilah!

I think something like this would work:

=IF(INDEX(COLLECT({Grant Fund},{Procurement ID},[Procurement ID]@row),1)="EDA SMART MEMS and ODHE Super Rapids",[Total Cost]@row/2)

The Index Collect part of this formula is collecting the information in the Grant Fund column from the row that matches the Procurement ID. The IF formula is asking: "If the information pulled = "text", then divide the information in the Total Cost column by 2.

Paul Newcome

The calculating "error" simply means that the sheet is still working. Here is a link to SS documentation on it.

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--calculating

.

Delilah

Hi @Monique

That formula did the trick! Thank you very much!