Please help with formula

[thelees]

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 thelees1@adelphia.net. 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]

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 thelees1@adelphia.net. 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

[thelees]

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 thelees1@adelphia.net. 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]

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 thelees1@adelphia.net. 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

[thelees]

I just wanted you to know how much I appreciate your help! I had taught all of
the Office Suite's apps at an advanced level in college and then was hired full
time about 5 years ago. I have been teaching 7th grade for the last 3 years,
computerized keyboarding and word processing. big difference:D

I still use Excel for my grades, which I use pretty advanced. I just never much
got into pivot tables. I've recently been asked to teach a class to non-public
school teachers on integrating technology into the curriculum. Would you mind if
I were to offer your site as a resource? I will have teachers at all different
levels. We will not have time for me to teach the application. I will have to
cover several apps and will only have about 3 hours. In this case, I will only
have time to show them a few things and will try to show things that novices can
grasp and is new to regular users. I haven't taught this group before, but I used
to teach work force training and retrain in business and industry, too.
Hopefully, I can still keep everyone happy.

Being able to offer some good resources would be great. You did a really nice job
on your site. Our school is making changes and making everyone conform to the
same type of website, but I have my own site: elainemlee.com. I would like to
add these to my site.

Thank you, again, Debra! I wrote with one problem in mind and ended up with great
ideas for that and something else :D This group is just super!

Elaine



Debra Dalgleish wrote:

> 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 thelees1@adelphia.net. 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

[Debra Dalgleish]

You're welcome! Thanks for letting me know that you like the information
on my site, and I'd be very pleased to have you offer it as a resource
for your class.

Good luck with your web site -- it looks like the transition is going well.

thelees wrote:
> I just wanted you to know how much I appreciate your help! I had taught
> all of the Office Suite's apps at an advanced level in college and then
> was hired full time about 5 years ago. I have been teaching 7th grade
> for the last 3 years, computerized keyboarding and word processing.
> big difference:D
>
> I still use Excel for my grades, which I use pretty advanced. I just
> never much got into pivot tables. I've recently been asked to teach a
> class to non-public school teachers on integrating technology into the
> curriculum. Would you mind if I were to offer your site as a resource?
> I will have teachers at all different levels. We will not have time for
> me to teach the application. I will have to cover several apps and will
> only have about 3 hours. In this case, I will only have time to show
> them a few things and will try to show things that novices can grasp and
> is new to regular users. I haven't taught this group before, but I used
> to teach work force training and retrain in business and industry, too.
> Hopefully, I can still keep everyone happy.
>
> Being able to offer some good resources would be great. You did a
> really nice job on your site. Our school is making changes and making
> everyone conform to the same type of website, but I have my own site:
> elainemlee.com <http://elainemlee.com>. I would like to add these to my
> site.
>
> Thank you, again, Debra! I wrote with one problem in mind and ended up
> with great ideas for that and something else :D This group is just super!
>
> Elaine
>
>
>
> Debra Dalgleish wrote:
>
>> 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 thelees1@adelphia.net. 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
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html