Basic invoice template
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:
How to create an array formula
- Copy (Ctrl + c) above array formula
- Select cell A2
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
How to copy array formula
- Select cell A2
- Copy (Ctrl + c)
- Select cell range A3:A100
- Paste (Ctrl + v)
Create named range
Named range formula:
Sheet: Payment
Create drop down list
- Select cell C2
- Go to "Data" tab
- Press with left mouse button on "Data validation"
- Select List
- Type =Names in Source field
- Press with left mouse button on OK
Get excel file *.xlsx
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Invoice category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
Templates category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
Excel categories
4 Responses to “Basic invoice template”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thank you very much. This solution has worked nicely.
Now, say if I have 1000 names alltogether. I guess I'll have to copy the array formula upto cell A1001. Also, I will have to increase the $A$500 upto $A$1001 in the Named Range Formula. Is that right?
Thanks again.
Hey Oscar,
I have done a slight change in my workbook. In Sheet:Calc, I have made a table in Column A and named it tblAllNames. Then I copied the array formula in its first row and adjusted the table rows as far as I needed (by pulling down the bottom right corner of the table). This way the formula copies itself in all the rows. Also, I can add names, which don’t exist in either of the Customer or Vendor tables, such as Bank Name or Employee Name, etc. I do have to remember not to sort this table because I tried that and it messed up things.
Then after this I created a Named range called AllNames and simply replaced the range by table name.
In its formula box I typed: =OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames"", ROW(tblAllNames), "A"))-1).
This worked fine.
Can you please explain how this formula works? Why do you have “A” in it?
By the way, what if I make another table called tblEmployee and want to add all employee names to my drop down list as well . I’m sure a lot of things are going to change in the array formula.
Rattan,
In its formula box I typed: =OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames"", ROW(tblAllNames), "A"))-1).
This worked fine.
Can you please explain how this formula works? Why do you have “A” in it?
You can also use "". The "A" is just a random letter. Max function handles only numerical values, remaining values are ignored.
Explaining the named range formula
The formula gets all values in tblAllNames. I can´t use counta function in this case.
Step 1 - Convert nonempty values to row numbers
IF(tblAllNames<>"", ROW(tblAllNames), "A")
Step 2 - Get the largest row number
MAX(IF(tblAllNames<>"", ROW(tblAllNames), "A"))
Step 3 - Get values from cell range
=OFFSET(tblAllNames, 0, 0, MAX(IF(tblAllNames<>"", ROW(tblAllNames), "A"))-1)
By the way, what if I make another table called tblEmployee and want to add all employee names to my drop down list as well . I’m sure a lot of things are going to change in the array formula.
Change formula in sheet Calc to the formula found in this blog post:
Merge three columns into one list in excel
Dear sir,
I have saved invoice with Party No in A column and Inv No in B column and untill N column the rest of the invoice data.
my qstn is when i enter Party no in textbox the all the invoices relating to that party should appear in listbox. how can i do that?