Google Sheets – Circular Dropdown Dependency

formulasgoogle sheetsgoogle-sheets-data-validation

Suppose we have 2 columns Id & Name which contained in sheet1. Now in sheet2 have dropdown for Id & Name which user can select and it Lookup value for each other (select Id from dropdown, Name automatically filled, and vice-versa).

Is this possible? Because it sounds like circular dependency.

Best Answer

It is indeed a circular dependency. You can either have ID dropdown or Name dropdown and counterpart of it will need to be secured by VLOOKUP formula:

=IFERROR(VLOOKUP(A2, Sheet1!A2:B, 2, 0), )

=IFERROR(VLOOKUP(B2, {Sheet1!B2:B,Sheet1!A2:A}, 2, 0), )

demo spreadsheet

Related Solutions

Google-sheets – How to specify “everything except the top row” as a range

There's an easy way. For example, for a sum, you can specify it like this:

=SUM(G2:G)

So basically, specify the first cell you want, then the name of the column. That way, you can skip as many rows as you want.

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

The pure spreadsheet answer is to create columns D and E to display your information. An example row:

A3: text
B3: (enter USD if known)
C3: (enter LBP if known)
D3: = if( isblank(B3) ; C3/1500 ; B3 )
E3: = if( isblank(C3) ; B3*1500 ; C3 )

The other way you can solve this is with JavaScript, if you know it, using the onEdit trigger. In that case the JavaScript would detect the edit to B3 and automatically add the formula in C3, or vice versa.

Best Answer

Related Solutions

Google-sheets – How to specify “everything except the top row” as a range

Google Sheets Formulas – Fixing Circular Dependency with IFERROR

Related Topic