Help with linking???

[Lisa]

I am trying to (I believe)link two worksheets. My goal is
to have information automatically update on my summary
sheet. I have a sheet of sales turned in for each day in
the month of May. There may be 3 or more sales for any
given day. Such as: 3 sales on May 18. One for 1200,
one for 2500, and one for 1500. I have a summary sheet
with each day in May listed. I add the total for all
sales on May 18 and enter it in the May 18 spot on the
summary. I want to set this up so that it will
automatically find and add all sales for a given day and
transfer the total into the summary sheet. I am having
trouble figuring out the best way to do this, since I may
have no sales or 10 sales on any given day. I have tried
using an IF statement, but I keep getting the #VALUE!
error. Please HELP!!!!!!!!!!!!!!!!

 

[John Michl]

Linking to other sheets can be tricky, especially when there are many links,
many files and the file names and dates could change month after month. It
wasn't totally clear if you had multiple sheets (tabs) within one workbook
(file) or multiple workbooks.

Given the little I know about your situation, I'd change the approach.
Start with one sales sheet instead of one for each day of the month. All
new sales go into this sheet. Use this as a database to drive other summary
sheets. For instance you might have one for daily detail and another for
daily subtotal.

The daily subtotal report would list all of the days of the month, number of
sales and dollars. You could create this with a pivot table (my preference)
or list the dates in column a and use sumif formulas to total the sales and
dollars.

The daily detail report would replace all of your individual sheets. Again
you could use a pivot table to display the data. Use the date field as a
"page" so you can select any date in your database and display the matching
sales and summary information. Instead of a pivot table, you could create a
query that pulls a given day's data from the database sheet, and puts it in
the report sheet. Perhaps there is a cell at the top in which you enter the
date you want for the report so when you refresh the data you'd pull only
the sales detail that matches the date.

If you really want to link to 28+ tabs or files (sheets or workbooks),
consider using the indirect function to create the formulas that do the
linking. It will make the worksheet(s) much easier to maintain or use in
the future such that you can enter the Month in a particular cell (such as
"June") and all of the formulas change so they look for a file or sheet
beginning with "June" and ending with a certain date.

I do a little freelance consulting in setting these things up. Check my
web-site for contact info if you'd like to discuss some of these ideas in
more detail. The actual implementation is a bit to involved to type in a
message.

John
www.JohnMichl.com