Related Row Lookups in the Header Area

For invoices, quotes, work orders, or other forms, it IS possible to define a Related Row data type in the header area of the spreadsheet, for choosing a Contact Name from a separate customer list database contained in the same workspace. Done - works great.

Next step: Is there also a way to use VLOOKUP or some other function to do a lookup based on the Customer ID to return the address information? If so, what is the syntax for referencing a range in a different worksheet? Something like this?
=VLOOKUP(lookup_value,[workbookname]sheetname!A1:D100,3,0)

Note that this is not the same as a Related Row Lookup because it is not within the Table area. This information is in the Header area.

One idea would be to create a new function for use in formulas that would work like this: =RRLOOKUP(related_row,field_name)
where related_row is a cell reference to a Related Row data type (the Customer ID in this case) and field_name is either the column number or name you want to return.

1 Like

Yes agreed. We may introduce an Object Oriented syntax. Something like Customer.[@PinCode],
Where Customer is a ‘Variable’ - similar to a Named Range.

1 Like

Hi Mohan,
I tried to copy (through automations) a value from worksheet1 to worksheet2 in a Related row column to enable related row lookup to serve the purpose of lookup from another workbook.
It seems that the values are not copying on the column marked for Related Row.
Is there a possibility to enable this functionality - to avoid copying the same data in multiple sheets.

Hi @Chandrasekhar_D - Checking with the team - Will update this thread.