Help with a potentially complicated formula.
Looking for a formula to do the following with these two sheets. Thank you
Best Answer
-
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.
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
Answers
-
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 -
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.
-
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)
-
Hello,
Thanks for this. I tried putting it in and it says incorrect argument. I tried tweaking it a bit, but still not working.
-
Does MATCH or INDEX need to be included somewhere?
-
If you are getting that error, double check your ranges are set correctly.
-
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?
-
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.
If my comment helped you, please help others by marking it as an accepted answer and consider helping me by clicking the 💡Insightful or ❤️Awesome buttons below!
Monique Odom-Stearn
Business Process Excellence Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
The calculating "error" simply means that the sheet is still working. Here is a link to SS documentation on it.
.
-
Hi @Monique
That formula did the trick! Thank you very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!