Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

MS Excel trick


  • This topic is locked This topic is locked
6 replies to this topic

#1 gmobcc

gmobcc

  • Members
  • 37 posts
  • OFFLINE
  •  
  • Location:New Orleans, Louisiana
  • Local time:02:00 PM

Posted 04 May 2005 - 03:13 PM

I am doing a project for work and need to imput a row with a total at the end every month, also I would like to show the total at the bottom of the page. My question is HOW do I make the moving mothly total appear in the same cell at the bottom of the sheet every time??


example

1) charges for month of January................................................100.00
2) charges for month of February.............................................. 350.00








Total 350.00

Thanks
Holly rusted hard drives Batman !!!

BC AdBot (Login to Remove)

 


m

#2 jgweed

jgweed

  • Staff Emeritus
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:02:00 PM

Posted 05 May 2005 - 01:18 PM

Are you putting every month on the same spreadsheet, or using a separate spreadsheet for each month? Is the data on a separate monthly page(s) and you use another spreadsheet page to format and print the data? I am trying to get a clearer picture of the process you are using and how you have designed your spreadsheet (s)....
Regards,
John

Edited by jgweed, 05 May 2005 - 01:25 PM.

Whereof one cannot speak, thereof one should be silent.

#3 gmobcc

gmobcc
  • Topic Starter

  • Members
  • 37 posts
  • OFFLINE
  •  
  • Location:New Orleans, Louisiana
  • Local time:02:00 PM

Posted 05 May 2005 - 03:08 PM

Are you putting every month on the same spreadsheet, or using a separate spreadsheet for each month? Is the data on a separate monthly page(s) and you use another spreadsheet page to format and print the data? I am trying to get a clearer picture of the process you are using and how you have designed your spreadsheet (s)....


Yes every month I will add a new row to the same spreadsheet and I want to be able to record the current total at the bottom of that sheet. The data is new every month and this is the only place that is being recorded. Hope this helps.

Thanks for the help
Holly rusted hard drives Batman !!!

#4 jgweed

jgweed

  • Staff Emeritus
  • 28,473 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Chicago, Il.
  • Local time:02:00 PM

Posted 05 May 2005 - 03:14 PM

Then you are just copying, if I understand your first example, the data in the current month row somewhere else, or do you want a YTD total of the column values? I guess I am confused by what you mean by "total."
Regards,
John
Whereof one cannot speak, thereof one should be silent.

#5 gmobcc

gmobcc
  • Topic Starter

  • Members
  • 37 posts
  • OFFLINE
  •  
  • Location:New Orleans, Louisiana
  • Local time:02:00 PM

Posted 06 May 2005 - 09:34 AM

OK it is simple. Every month I will have add a new row with a new total for that month. Now that total I want to copy it to the bottom of the page. That way I will have a "current" total for the "current" month there are no calculations involved its just a matter of having a cell reference another cell ( =D4 ) at the bottom of the page. Is there a way to make this process automatic??

Thanks
Holly rusted hard drives Batman !!!

#6 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,568 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:02:00 PM

Posted 13 May 2005 - 08:14 PM

I am doing a project for work and need to imput a row with  a total at the end every month, also I would like to show the total at the bottom of the page.  My question is HOW do I make the moving mothly total appear in the same cell at the bottom of the sheet every time??


example

1)  charges for month of January................................................100.00
2)  charges for month of February.............................................. 350.00



                                                                                      Total  350.00

Thanks

I, too am having problem understanding your problem. There are many ways of doing a total in place of your choosing but I can't see what method would fit best based on your description.

Let's start with few simple things:
1. Do you really have data as shown here and am I correct assuming you have that data in one column, dots and all, say, column A

example

1)  charges for month of January................................................100.00
2)  charges for month of February.............................................. 350.00

If so, split it out as you can't add text. Put 100 and 350 in a separate column.
If you need help to split it, please say so.

2. define Total. Do you mean last entry, as your example suggests? Or total of monthly entries in which case a simple sum function does the job.

3. perhaps based on your last answer which is still not clear to me, what you need to figure out is last row number where the data is, and reference that row. Is that what you're after?

4. define "bottom of the page" - paper size, number of rows that fit on a page, font you're using ... this can get nasty and inflexible, a redefinition of a page footer might be in order.

I don't think you can possibly get an answer here when you do not describe what's where and what you want :thumbsup:

#7 gmobcc

gmobcc
  • Topic Starter

  • Members
  • 37 posts
  • OFFLINE
  •  
  • Location:New Orleans, Louisiana
  • Local time:02:00 PM

Posted 16 May 2005 - 09:27 AM

I don't know why this is so confusing. I posted the same question on another website and 1 hour later got a solution..........here it is:


Assuming that B1:B12 contains your monthly totals, and you'd like your moving total displayed in B13, try the following formula in B13...

=LOOKUP(9.99999999999999E+307,B1:B12)


Thanks for trying anyway.
Holly rusted hard drives Batman !!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users