Finding data from multiple woorkbooks in Excel?...

  ShorN 10:47 04 Apr 2005

Hoping someone can help!?.....

At work we get invoices on an excel spreadsheet.
The spreadsheet contains company details, a reference number, and the amount invoiced. We then check each item on the invoice and mark on the spreadsheet whether or not the payment will be made.

I want to know if there is a way that when we get a new invoice for the month we can search the previous months invoices to check that they have not been invoiced again.

Anyone able to help with this??
Thanks in advance!

  pauldonovan 11:01 04 Apr 2005

..that each month you start a brand new excel workbook ?

If they were invoiced again, would they have the same invoice number or does it need to be clever and look up amount, company name etc.?

  ShorN 11:21 04 Apr 2005

Each month is on a new workbook,yes.

Basically i would like some way to search for the reference number of the company that it on the current invoice, and then get a list of every previous month that it has come up on invoices.

(the reference number is the unique number we hold for the company)

If there was a way that the search could bring up any further info that would also be useful.

  pauldonovan 11:32 04 Apr 2005

... a rather 'hard-coded' way would be to put vlookup formulas alongside each invoice entry to do a lookup of the reference to prior months. You'd have to amend each month. Are you familiar with vlookup?

To make it dynamic, I fear the only real way would be to load them into access or something like that OR you could write a macro that 'knows' what all the different months are likely to be called, opens them up and does a find in them.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

Visual Trends 2018: This year’s must-know colour, design, branding & photography trends

iMac Pro review

Apple Music : comment obtenir 3 mois gratuits ?