Author: Oscar Cronquist Article last updated on March 13, 2019

Rattan asks:

In my workbook I have three worksheets; "Customer", "Vendor" and "Payment".

In the Customer sheet I have a table, tblCustomer, where I add new customers. Similarly, in the Vendor sheet I have a table, tblVendor, where I add new vendors.

In the Payment sheet I have a table, tblPayment, where i have three columns; Date, Amount and Name.

Now, here is what I want to do; In the Name column of the tblPayment, I want to create a drop down list in each cell, which would contain all the names from tblCustomer[Name] and tblVendor[Name].

This way I can fill in the Date, Amount and then select one of all the names available in the drop down list of my Name cell. Is this possible without using VB code or any macro? If so, please help me out with this.

Answer:

Yes, it is possible to merge values from both  Excel defined Tables without using VBA, however, you need a "Calculation" sheet containing a formula that extracts the values from both sources to one distinct column.

A dynamic named range will then be used in order to get all values from the calculation sheet and inserted to a drop-down list.

Sheet: Customer

Sheet: Vendor

Sheet:Calc

Array formula in cell A2:

=IFERROR(INDEX(tblCustomer[First Name], ROWS(C1:$C$1)), IFERROR(INDEX(tblVendor[Company Name], ROWS(C1:$C$1)-ROWS(tblCustomer[First Name])), ""))

How to create an array formula

  1. Copy (Ctrl + c) above array formula
  2. Select cell A2
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. Release all keys

How to copy array formula

  1. Select cell A2
  2. Copy (Ctrl + c)
  3. Select cell range A3:A100
  4. Paste (Ctrl + v)

Create named range

Named range formula:

=OFFSET(Calc!$A$2, 0, 0, MAX(IF(Calc!$A$2:$A$500<>"", ROW(Calc!$A$2:$A$500), "A"))-1)

Sheet: Payment

Create drop down list

  1. Select cell C2
  2. Go to "Data" tab
  3. Press with left mouse button on "Data validation"
  4. Select List
  5. Type =Names in Source field
  6. Press with left mouse button on OK

Get excel file *.xlsx

Basic invoice template.xlsx