|
|
|
|
You're welcome! There are instructions for pivot tables in Excel's Help,
and Jon Peltier has information and links:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
There's information on AutoFilters on my site:
http://www.contextures.com/xlautofilter01.html
thelees wrote:
Thank you, very much. My mind was heading toward Access. I was thinking if
there was a way to do it in Excel, he would need macros, which I don't know
how to do. I didn't even think of the obvious, but I've never used pivot
tables very much at all. I really appreciate your help.
Debra Dalgleish wrote:
With 185 customers to track, Access might be a better program to use. If
he really wants to do this in Excel, it may be easier to keep all the
data on one sheet, with an additional column to record the customer
name. Then, use an AutoFilter to view the data for a specific customer,
and use a pivot table to summarize the data.
thelees wrote:
It has been a long time since I've needed to write for help, but I need
it now. I had a friend come to me for help with a spreadsheet he's
creating for the food service company he works for. While I taught
advanced Excel in college, that was four years ago, and I don't recall
this particular issue.
The first worksheet is a payment summary sheet. Next, there are sheets
for (invoices) for each organization the food service company deals
with. On each invoice sheet, there are cells which identify information
common to each of these company sheets. All of the invoice sheets are
the same.
The summary sheet holds, of course, information equal to cells on each
of these sheets. From left to right, there is a cell identifying the
date of the invoice; name of the individual in each organization that
the food service company works with; the amount due; and payment info:
amount paid, ck #, date paid. Column A is simply numbers which reflect
the name of each sheet, but these numbers were just typed in. (I'd be
curious whether there is way to automatically have Excel enter each new
sheet name, but this wasn't really 1 of the question).
First big question. To start with there a way to set say, Invoice 1 on
the summary sheet with C1= sheet 1, cell $D$4 and then copy that formula
down so that the sheet is relative? So that on the next row down, the
formula will reference cell $D$4 on sheet 2? That is really what needs
done with each of the last 6 columns, too.
Second big question. He deals with 185 organizations. Is there a way
for him to make new sheets for each one so that he can copy each sheet
and when it is created it is automatically renamed sequentially?
Incidentally, I believe they are using Excel 2003, which I was just
starting to learn, but then had to have some work on the computer
holding that version.
Are these viable questions, or should he simply be using Access? I
think he prefers to work in Excel so I figured I'd ask and learn.
Thank you to anyone who helps! I am on a time crunch here. I am going
away for the weekend early tomorrow morning and not getting back till
late Monday night. If at all possible and you all aren't leaving
tonight, besides posting to the group, could I please request a response
to my email at . Sometimes there are so many
posts, I can't find one I'd see early on. Again, many thanks, in
advance!
Elaine Lee
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
|