Cross-sheet formula help required

Vivien Chong

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?

jmyzk_cloudsmart_jp

Hi @Vivien Chong

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.

https://app.smartsheet.com/b/publish?EQBCT=1c6d769219ca449d804be529ddf57a2e

Link to published Project MetaData Sheet

https://app.smartsheet.com/b/publish?EQBCT=e61957fc30134a8bb92d6ed1bea2c00e

jmyzk_cloudsmart_jp

Hi @Vivien Chong

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.

https://app.smartsheet.com/b/publish?EQBCT=1c6d769219ca449d804be529ddf57a2e

Link to published Project MetaData Sheet

https://app.smartsheet.com/b/publish?EQBCT=e61957fc30134a8bb92d6ed1bea2c00e

Vivien Chong

@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:

jmyzk_cloudsmart_jp

@Vivien Chong

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.

Vivien Chong

@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.

jmyzk_cloudsmart_jp

@Vivien Chong Sometimes these quirks happen. Glad to hear it's working correctly now! 😁