Searching across muliple worksheets

[Jerry Selinger]

I have my invoicing set up with Excel. Need to search all invoices for a
total for the Canadian Gst. The total for Gst in each invoice is in cell L2.

Also is there a way to total certain items that I have invoiced. I have
assigned a code number to each unique item. E.G. Labour would be 513. Is it
possible to search across all invoices for code 513 and then total that
amount?

Thanks in advance

 

[random1970]

Jerry,

As it stands, I'd probably need some more detail.

Is each of your invoices a separate worksheet within a workbook?
If so, do you create a new worksheet each time you create a new
invoice?

If so, it would be difficult to do what you ask.

A redesign might be a better option. It shouldn't take too long and
would solve your problem.

Enter all your invoice data on one sheet and have a separate worksheet
set up as an invoice template. USE VLOOKUP (or HLOOKUP depending how
you set up your data) to fill in the invoice based on the invoice
number.

e.g. In your "Invoice Data" worksheet, Column A would be Invoice
Number, Column B Invoice Date, Column C Client Name etc. with as many
columns as there are pieces of information on your invoice, and as many
rows as you like (say 1000). Select and name this range "invoicedata"
Then, on your invoice template worksheet, cell A1 contains the invoice
number. The cell where the date should appear would have the fomula
=VLOOKUP(A1,invoicedata,2,false). The cell where the client name would
appear would have =VLOOKUP(A1,invoicedata,3,false) etc.

Once this is done, to bring up an invoice, just type in the invoice
number. (I'd lock all cells on the invoice sheet except the invoice
number, and protect it to avoid accidentally typing over formulas)

What this would setup would mean though is that all the data is on one
sheet. It should then be no problem at all to find the data you need.

Hope this helps. Let me know how you go.

 

[Jerry Selinger]

Thanks for your reply. Was thinking there for awhile that I would have to
re-post the question.

I think that I have done this ass backward. I'm not to sure what a workbook
actually is. I started by designing a master invoice. This is my template
for all my invoices. When I want to invoice someone for my services I pull
up this master invoice and fill in the necessary information and then rename
it ( save as ). I name my invoices by their number. I now have 130 invoices.
Each one is a separate spreadsheet. I'm assuming that each one is considered
a separate workbook. In reading the various help files I think that I should
have just used one spreadsheet and utilized the Tabs (Sheets) as the
individual invoices. Is that correct? If it is correct; is there a way to
merge all these invoices into one workbook? Hope this makes sense!!


Jerry

 

[random1970]

Jerry,

A workbook is a single Excel file, containing a number of worksheets.
The number of worksheets you can have in a workbook is "limited by
system memory" (a quote from Microsoft), so there's no definite answer
to that one.

You could bring all of your invoices together into a single file, by
opening and naming a new file (say "Invoices - XYZ Pty Ltd") then
opening each of your existing files (say 4 or 5 at a time). Once each
file was open, you could then copy each invoice (worksheet) to your new
workbook. In Excel, this would be Edit Menu, Move or Copy Sheet, check
the make a copy box, and select Invoices - XYZ Pty Ltd in the "To Book"
section, then click OK. Repeat for each of your 130 invoices and hope
you don't reach the point in "Invoices - XYZ Pty Ltd" where it's
"limited by system memory"

It could be done, but I don't suggest it. It would take some time, and
you'd still have your current problem of being unable to total from a
number of worksheets.

You're better off setting up all your data on one worksheet, and having
your invoices generated when required on a separate worksheet as
described in my last post. If your datasheet was set up properly, you
could also create a merge file in Word, and use this to print invoices.
With this set up, the number of invoices you could manage would be
limited only by the number of rows in Excel (65 536 - wouldn't that be
nice!)

If you like, send me a copy of the invoice master with some dummy data.
I'll add a worksheet before it to feed your invoice data in and send it
back. Have a look at the formulas and setup, and see what you think.
Alternately, have a go yourself and, if you can't get it to work, send
it through. More than happy to help.

Eddie

e-mail: (e-mail address removed)

 

[Earl Kiosterud]

Eddie,

First of all, I hope that's not your real email address. The spam bots will
pick it up, and you'll be getting literally hundreds of spam emails a day at
some point.

Your suggestion of putting all the data into one sheet rather than having
separate sheets for each invoice is on target. But doing invoices with one
table doesn't work well with invoices, where you have line item records
(quantity, description, etc.) and invoice data (invoice number, customer
information, etc). You really need a line item table and and invoice table.
Multiple table solutions are doable in Excel, but require a bit of work, and
are a bit messy. You wind up with a lot of VLOOKUP's to get the invoice
data and the line item data together. For this application, a data base
program would be a whole lot more straightforward.

Earl Kiosterud
www.smokeylake.com

 

[vane0326]

See if this helps!

First the formula you must put the name of your worksheets in the range
of cells say in D1738. If not it will not work.



=SUM(SUMIF(INDIRECT(A2:A12&"!D1738"),513,INDIRECT(A2:A12&"!G17:G28")))



Must be confirmed with:

CTRL+SHIFT+ENTER

 

[Peo Sjoblom]

If there is one space in a sheet name that will return an error, this will
work regardless spaces or no spaces

=SUM(SUMIF(INDIRECT("'"&A2:A12&"'!D1738"),513,INDIRECT("'"&A2:A12&"'!G17:G28")))

or if entered normally

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A12&"'!D1738"),513,INDIRECT("'"&A2:A12&"'!G17:G28")))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[random1970]

Earl,

Point taken. A database option is preferable for sure because of the
multiple lines on the invoice. Took a bit of setting up, and a large
number of VLOOKUPS within the invoice data table. It worked though
(perhaps not efficient, but effective nonetheless). I must take the
time to learn Access at some stage.

E-mail address? What e-mail address? (Thanks for the tip)