Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Vlookup Question

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, all,

I need help!

I have Table1 and Table2 in Spreadsheet. In Table2, there are two or more Invoice Number under same Serial Number.
What I wnat is display the Invoice Number as Original Invoice Number in Table1 if Invoice Number in Table2 is different than Invoice Number in Table1 under same Serial Number.
I tried using Vlookup, but it just display the first value found.

Sample data is attached.

Please advise.

View Answers     

Similar Excel Tutorials

Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e

Similar Topics







Hi, I need a way to select a discontinuous range in VBA with these circumstances:

The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each

There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code

What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.

I can't seem to upload files here at work, but the tables look something like...

Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5

Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0

With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2

Thanks in advance, sorry for the mess


I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.

Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:

Code:

                      A                       B                      C                      D
1
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)


To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns B-D, but doing that gives me the result below:
Code:

                      A                       B                      C                      D
                      A                       B                      C                      D
1
2                    10                      10                     10                     10
3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2)
4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3)
5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4)
6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)


As you can see, I still have to go in to Columns B-D and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?


Hey

I use excel file for my Invoice. Here I need to move up & down to edit the previous & next invoice. I made to select the invoice number from combo box.

So when I select the Invoice number from combo box that the particular Invoice should be
display. I tried many code but i caqnnot.

Can u please help me?

Regards
Charles


Hi,

I am looking for some suggestions on how can I possibly withdraw main data from my Invoice template (like: Invoice number, Name of the customer, total amount) in to another spreadsheet in order for me to create some sort of data base of every invoice that I send to my customers. I also wonder if some how I can auto increase my invoice number? For example if you open new invoice with number 001 so that the second one that you open will be automatically changed on 002 and so on, but with question If you reopen an existing invoice the number should not increase then.

I know that VBA can help but since I do not know anything about VBA I am looking for some help here.

Thanks in advance

I have an invoice sheet and when one job is entered into it I would like a new invoice sheet with the same format to appear while the previous invoice is filed away possibly in another workbook. The jnvoices are all numbered so I'm also attempting to have the invoice number grow by 1 everytime a new automatic invoice is created. Any ideas attached is the invoice for further analysis.


i am complete beginner.
i am currently designing an invoice in excel.
i want a macro that will automatically save an invoice worksheet from its invoice number which will clear the sheet for a new invoice with a new invoice number. the worksheet should then be saved in my documents.
how do you create a macro that automatically generate a new invoice number from the previous number. for e.g. 101 going on to 102
i have searched through the forum and found solution but it doesn't work for me.
thx


I am using an Excel based invoice and I want to add a serial number that will
automatically update each time I open a new invoice. Is there an easy way?
Thanks





Hi

I am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.

For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.

I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")

Does someone mind having a look in to the attachment and letting me know what I have done incorrectly and how I can fix it?

I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.

Thank you
Narelle

Can any body help

I have created a automatic invoice generator which will print the invoice and then clear the invoice and generate the next invoice number

The worksheet consists of 3 spreadsheets Invoice Lookup tables which store the products and clients Invoice list with get a grandtotal of invoice plus key information ie name etc

The Invoice no is situated in cells D1 to E1 but what i would like the macro to do is to copy the invoice only and save it in a directory C:\oldinvoices with a file name of the invoice no ie cells D1 - E1 this will enable me to be able to recreate an old invoice if needs be

I cannot see how this can be done and would appreciate any advise that yon can give me


I have created a few identical sheets, namely Invoice, Invoice (2), Invoice (3) and so on. Cell F5 of each invoice will be the invoice number (running number). After sheet Invoice (3), i have created another sheet namely Invoice Summary (on the right to Invoice (3)), which will have one of the column, says column C to capture invoice number from sheet Invoice, Invoice (2) and Invoice (3) by draging down column C2 to C4 of Invoice Summary sheet:

C2 to reflect invoice number of sheet Invoice
C3 to reflect invoice number of sheet Invoice (2)
C4 to reflect invoice number of sheet Invoice (3)

however, relative sheet reference could not be perform by draging down the fill handle.
How do i solve this?
Thanks in advance!


Hi.
I am processing invoices and i need to know if the same invoice number has been used in the spread sheet. For example. Invoice number in each cell down a colum. as below
Invoice Number
1
2
3
4
5
6
7
8
9

If the same invoice number is used an alert or dialog box box saying that that number has already been used appears. for example
Invoice Number
1
2
3
4
5
6
4 (when i try to enter or move on from this cell I am made aware the number has already been used.)
Thanks for any help you may be able to provide.
Regards
Rusty


Sorry, I didn't quite know how to word the title .

I have an Excel Invoice Template, saved as a template.

I have code that generates a new invoice number each time the template is opened in VBA Editor. The auto numbering system works great! However.... (here comes the sad part)

After I enter all the data I need on the invoice, I want to save a copy of the invoice in a different file folder, which I can no problem. The problem is, if I re-open the saved invoice, we will call it Invoice #100, excel asks me if I want to update, if I say either yes or no, the invoice number will change to Invoice #101. I'm sure you can see how this can cause a major problem if we need to compare information later on, finding the correct invoice would be almost impossible as it would not match the customers invoice number.

What I need to know is:
Is there code I can add to the existing code, to stop the increment on a saved invoice, but not on the original template?

I have gotten so much information from this site Im so glad it is here and hopefully I can help someone out in the future

Michelle


I have a spreadsheet I am using to simply record invoice numbers and the times that I completed those numbers. Instead of manually writing the time I recorded the invoice number, is there a way that when I enter the invoice number, the time I entered it will populate in the cell next to the invoice number automatically?

Invoice Number Time 456985 1:59


Hi.
I've been playing around and cannot find a formula for an invoice. What I am after is as follows:
Invoices to looking like: 08-09/01......08-09/02 etc etc. The 08-09 is the current tax year and obviously the munber after is the invoice number. Now what I am trying to do is after I've closed an invoice, say 08-09/02, then when I open up a new excel invoice, the invoice number should automatically change to 08-09/03.
Hope all this makes sense.




I have a very simple macro that I found on the internet that increases an invoice number by 1.

Sub NextInvoice()

Range("L1").Value = Range("L1").Value + 1

End Sub

It works great, provided the invoice number is just numbers. But I want to be able to add PF as in PF101425, on some of the invoices, not every invoice, however when I put the PF there the macro does not work.

There is more to the macro than just increasing the invoice number. It also populates other cells on a separate sheet with the information from the invoice and then clears all fields ready for the next invoice. So when I click on the button that triggers the macro the fields are populated correctly, even with the PF in front of the number, it is just the invoice number does not increase by 1.

If anybody can help with this problem it would be appreciated. Thank you.



Dear all,

Please I am new to this forum the site is still strange to me.

I need a macro that on invoice template will add new invoice with number MIL-EI-0001 and on, and then paste specific data of the invoice in invoice register with the invoice number hyper linked and clear specific data in the invoice template.

Best Regards,
Daotor.

I have created an invoice on excel and would like to put a button on the page which will advance the invoice numbers when clicked.
for example I am currently wring up an invoice which is number 10045 when this invoice is completed I would like to be able to just click a button and the invoice number would move forward to 10046 (using the same spreadsheet)


Hi,
I am tiring to create an invoice template which can help me load and create invoices. I need 3 things to be done here.
1. Auto creates invoice number: the invoice number should look up the last record in the different sheet (invoice details) and add 1 to it.
2. Save the invoice created to the different sheet (invoice details), and if I click on create invoice without saving the last invoice it should ask to save.
3. Get invoice details from the different sheet(invoice details).

Sheet names are Invoice and Invoice details.

Hello,

If i have invoice template A1:I55, in cell I6 we have invoice number, how can we make I6 serial number with save option to save every invoice we made ?

Thank you,


Hi everyone,

My first post and I am an excel newbie. I have created a client database in Excel 2007, which I have attached. I wish to populate a client invoice in a separate workbook with client address information, invoice item and price based upon the invoice number in Column B. The invoice numbers simply increase incrementally for each database entry. The information that I wish to use to populate the invoice is contiguous in columns C through I, however the locations that I wish to populate the invoice with are not contiguous (other than the client address lines) I simply wish to use the data in the row corresponding with the invoice number to populate the invoice.

I preferred to create the invoice in a different workbook as it is then simpler to convert this to a pdf document format.

I have attached the client database and a sample invoice. I would be grateful for any assistance that anyone could offer.

Many thanks


Hi all,
I am a novice wrt Excel. I had created a simple Invoice, to save as a
template. Would like to have the Invoice Number cell automatically in
running number, for each invoice I created. Example my first invoice will
have the number 001. Once finished and saved, opening up another new
invoice, using the template, it would now show 002, and so on.

Truly appreciate some advise to do this.

Thanks in advance.

AlFlo





hey guys, i'm having 3 problems with this spreadsheet could you help me out?

1. how to i manage that the invoice number would +1 everytime i click Clear
2. In the invoice sheet, there should be a button to add name, date, no. of items, prices and invoice no. to the sales journal.
3. I've tried adding line, then copy the details into sales journal but there's an error due to the invoice number.

please help. thanks!


Hello!

I'm creating a sheet for a football league, I want that when I insert the results in table1, the table2 automatically fills up.

Is it possible? Should I change something in table1 first?

table1.jpg

table2.jpg

Thanks!


Hi there,

I need help please, i have this spreadsheet that i need automatic invoice numbers created. I have read around for help and tried it, but still can't get it right. Please can someone take the sheet for me and add the code?

I need it to create a new number every time the sheet is opened and then save that invoice number to a separate notepad file just for record.

There are 3 sheets that i need the same invoice number on...

On "Bulk Del" i need the invoice number in cell "AS1" next to "trip No."
On "Bulk Check" i need the same invoice number in "E2" under "trip no."
On "Offload Check" i need the same invoice number in "C2" under "trip no."

The whole book gets printed with all three documents as one set of documents....

The link to the file is : http://www.sendspace.com/file/gqnryl

Msg me for my email addy if you willing to help me

I would appreciate the help big time!!!

Thanks


I have attached a spreadsheet to help explain what i am trying to achieve. You would need to open the spreadsheet to understand my question.


I would like excel to be able to automatically assign the "Invoice no" from the sales sheet into the stock sheets "111" and "222" as appropriate.

Also the invoice number would need to be assigned at the lowest empty stock cell.



For the solution i think the answer would not be a formula.

A solution that would be very help could be a macro where i select the invoice number and activate the macro with a shortcut when will then assign the invoice number against the items. This would be done individually per each invoice number.


This may be an unusual request, but your help on this one would be much appreciated.

I look forward to your solutions.

Thanks