Cross-sheet formula help required
I am using the formula from Smartsheet template Metadata sheet to capture data from intake sheet. However, one of the column doesn't seem to work, anyone has any idea what went wrong?
This is the Metadata sheet, the data in red is incorrect. I am using this formula across from "Created by" to "Description":
=INDEX({Portfolio Intake Reference}, MATCH($[Project ID]@row, {Portfolio Intake Project ID}, 0), MATCH([Created by]$1, {Portfolio Intake Header Row}, 0))
Instead of showing "Harley Sterling", it is displaying the "Financial Systems Upgrade".
This is the Intake Sheet:
Anyone has any idea?
Best Answer
-
It took some time to understand your formula, but it essentially references a two-dimensional range with the INDEX. Project ID defines the row or vertical position, and the column or horizontal position is defined by the position of ([Created by]$1 to [Description]$1 in the {{Portfolio Intake Header Row} range.
Since the formula is not wrong, you must check if your ranges are correct. I suspect either {Portfolio Intake Reference} or {Portfolio Intake Header Row} is wrong, as your formula references the wrong horizontal position.
Alternatively, you may have had some errors when dragging the relative referencing formula horizontally.
Link to published Project MetaData Sheet
Answers
-
It took some time to understand your formula, but it essentially references a two-dimensional range with the INDEX. Project ID defines the row or vertical position, and the column or horizontal position is defined by the position of ([Created by]$1 to [Description]$1 in the {{Portfolio Intake Header Row} range.
Since the formula is not wrong, you must check if your ranges are correct. I suspect either {Portfolio Intake Reference} or {Portfolio Intake Header Row} is wrong, as your formula references the wrong horizontal position.
Alternatively, you may have had some errors when dragging the relative referencing formula horizontally.
Link to published Project MetaData Sheet
-
@jmyzk_cloudsmart_jp I have checked my formula, it is same as yours. The formula works from Project Name to Project Owner, but somehow is not pulling the right information for Created by.
If I follow your Portfolio Intake Reference, the entire reference is wrong. as follows:
-
Strange...?
I could recreate your problem by copying & pasting the Project Name columns formula to the Created By column.
I dragged the Project Name columns formula to the Created By column with the one working correctly.
-
@jmyzk_cloudsmart_jp strange indeed.
I did the formula at "created by" column and drag it across the rest. Truly strange that all the rest are capturing the right data except "created by". However I don't know what I did yesterday and it is now showing the right data from Created by to Project Owner.
Anyway, thanks for assisting to find out what could go wrong. Appreciate your time and effort.
-
@Vivien Chong Sometimes these quirks happen. Glad to hear it's working correctly now! 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!