-1

I have a spread sheet which can be viewed at https://docs.google.com/spreadsheets/d/1bhIV1ULLXhjdSO_5Q5l5ZNe7Zaxrj15CYMW88FMFgRU/edit?usp=sharing

The way the spread sheet works is when a selection from the list is made the specific cells reference a data table and fills in the respective cells. This works perfectly in excel and I just use Iferror to hide the circular references. This is not the case in google sheets as the #REF! error still appears. How can I rectify so that they no longer appear? I have tried unique but it makes all the cells bank even when a different selection from the drop down box is made.

Cage
  • 11
  • 1
  • 1

2 Answers2

1

You can use the ISREF() function for this. Combine it with an IF() and you're good to go:

IF(ISREF('sheetname'!A4), 'sheetname'!A4, "")

Bramus
  • 1,732
  • 14
  • 19
0

The problem is that the formula itself is fine but (for column N) 'Attributes-InSeason-Trade_Mach'!$L121 and the following rows evaluate to "".

This means for INDEX in Google Spreadsheets that it will use all rows which in turn would overwrite the data in the next row because INDEX will be returning a range, not a single cell. The Formula itself is evaluating fine, it does not cause an error which is why IFERROR does not trigger the alterantive "", the problem happens after the evaluation when it is trying to display the data.

You can just wrap the INDEX Call inside of an IF like this (This is an example for Column N)

=IFERROR(IF('Attributes-InSeason-Trade_Mach'!$L110 <> "", 
            INDEX('Attributes-InSeason-Trade_Mach'!$H$110:$I$214,
                  'Attributes-InSeason-Trade_Mach'!$L110,
                   COLUMNS(Trade_Machine!$P$4:P4)), ""), "")
Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37