Help with a potentially complicated formula.
Looking for a formula to do the following with these two sheets. Thank you
Best Answers
-
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
Architecture Solutions Manager
Smartsheet Leader & Community Champion
Pronouns: She/Her (What’s this?)
“Take chances, make mistakes, get messy!” – Ms. Frizzle
-
Hi @Monique
That formula did the trick! Thank you very much!
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
Architecture Solutions 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.
Formula error messages | Smartsheet Learning Center
.
-
Hi @Monique
That formula did the trick! Thank you very much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi all! I know how to do this manually but am hoping someone has a shortcut! I have a sheet with a very long dropdown list of college majors for employees who have signed up for a particular program. …
- New program proposals are submitted via a form and collected on Sheet 1. When approved by a team leader, the row is copied to Sheet 2. I want to capture the date the copied occurred, which I thought w…
- I need to update a value in a new column. How would I look at a date column that shows dates between 01/01/2025 to 03/31/2025 and place the value of Q1_2025 into another cell?