Author: Oscar Cronquist Article last updated on August 30, 2017

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.


Yes it is possible without using vba.

Sheet: Customer

Sheet: Vendor


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. Click "Data validation"
  4. Select List
  5. Type =Names in Source field
  6. Click OK

Download excel file *.xlsx

Basic invoice template.xlsx