Best Of

Looks like your #Unparseable error may be due to this extra comma with no parameter to follow. Remove it completely or add a column number for the Index function.

You need to add another IFERROR to the front.

Right now your formula is saying to run the first INDEX/COLLECT. If there is an error with the first one, then run the second one. That leaves your final piece of , "---" just hanging out there.

Try this:

=IFERROR(IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 1)), "---")

@AlexisJ This is still a SUMIFS. The formula written below is if the reference information is in the same sheet.

=SUMIFS(Cost:Cost, [Requestor Email]:[Requestor Email], "jdoe@company.gov", [Wish List]:[Wish List], 1)

IF The Requestor Email is not jdoe@company.gov or Wish List is not Checked, then the SUMIFS formula is false and will not perform the calculation.

@mahmadi

I use the following method to achieve this in the Core App only.

1. Sheet for display
   1. Auto number Row ID column.
   2. Read-only columns and editable columns for 20, say 40 people. (in my demo).
   3. 20 columns for 20 people to display the URL of the form created in step 2.
   4. Add formulas that retrieve the latest data from the sheet of the form created in 2 by INDEX (MATCH or COLUMN) using the row ID as a key.
2. Sheet for forms to update the sheet for display
   1. Create a form for every 20 users that adds a new row with a row ID column and editable columns.
   2. For Row ID, use a URL query string to prefill default values on a form, using 1. Sheet for display's Row ID.
      1. https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
3. Set up the form so that new inputs are added to the top of the sheet. (To make it easier to get the latest additions by the form in INDEX 1)
4. A report for each person that pulls only the relevant columns from the sheet and displays them
5. Create a dashboard for each person, add published reports in 3, and share the dashboard with view-only permissions.
6. Steps 1 to 4 will allow the person with view-only access to the dashboard to view and edit some columns of the sheet in 1 without having access to the above sheet.

Below is a demo dashboard I created using this method so you can try it out.

(Originally, this would only be shared with those who can view and edit as in 4, but for the sake of demonstration, we are making the dashboard available to the public.)