• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto calculate a spreadsheet

L

lreaide

New Member
Every month, I input totals for production, A/R, services, hours, etc. for two dental offices. This is done for the current month & current year, then compared against the last year and month. I calculate from January through the current month (YTD). I have highlighted the numbers for each current month's numbers across the spreadsheet's rows. I have formulas in place to calculate YTD. Is there any way to highlight the new month and have the spreadsheet automatically calculate using the new month added to the previous months?


Example: This month I will calculate using Jan 2012 through May 2012, this has been highlighted. Next month, I will use Jan 2012 through June 2012, moving my highlight. I manually go into spreadsheet and change the values for each formula to include the new month. Since I compare 201 with 2012, I have to update 2 spreadsheets(on one page); I compare month 2013 vs month 2012, then YTD 2013 vs 2012. Then, because we have 2 offices, I need to pull the numbers from one office, add them to the 2nd office and compare those number month vs month, then YTD vs YTD.


Because I manually change all the formulas, there is too much room for error. The spreadsheet themselves work very well and the format of the spreadsheets work for my needs except for manually re-doing the formulas each month - there are about 30 of them!


Is there a way to get each highlighted row/formula to recalculate each month?
 
Hi ,


Certainly. If you can upload your file , the formulae can all be reworked so that you don't have to redo them each month.


Narayan
 
Thank you! Here are the links to my spreadsheets.


I use the Lexington one first, changing the highlighted row to reflect the month in the previous year and the current year.

I add figures for current month, then manually update the formulas to reflect the cells in the months being used.


I then go to the Sandusky sheet. I do the same, highlighting current months, adding new figures, manually changing the formulas.


At the bottom of the Sandusky sheet, I have to add Lexington and Sandusky figures for the months and compare last years figures with this years figures. I manually add the numbers, put them in the spreadsheet.


If I could automate the Sandusky spreadsheet to automatically pull the numbers from the Lexington spreadsheet, that would help.


So, I need to be able to highlight the current months' numbers in both last year and current year, have the formulas reflect that change. Then, I need to pull the month and year numbers from Lexington, add them to the combined row and figure then new combined totals.


I have the sheet worked pretty well as far as I could. The formulae work as I need them to but I don't know how to go to the next step and further automate my spreadsheets.


Any suggestions?


I appreciate your help; like I said, I have gone as far as my limited knowledge will take me.


Thank you,


Kim Courtney


http://rapidshare.com/files/1232547647/Copy%20of%20Lexington%20Practice%20Monitor%20kims%20working%20copy.xls


http://rapidshare.com/files/2658489772/Copy%20of%20Sandusky%20Dental%20Care%20practice%20monitor%20kims%20working%20copy.xls
 
Hi Kim ,


When I click on the links , I get this message :



The download for this file has not been enabled. Only the user who uploaded it, can enable the download option.




Can you give others permission to access and download your files , and then post the access links here ?


Narayan
 
http://www.sendspace.com/file/wkwmjv


http://www.sendspace.com/file/zega40


I hope these work! Should be Lexington and Sandusky Excel spreadsheets.
 
Hi, lreaide!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Give a look at this files:

https://dl.dropboxusercontent.com/u/60558749/Copy%20of%20Lexington%20Practice%20Monitor%20kims%20working%20copy%20%28for%20lreaide%20at%20chandoo.org%29.xls

https://dl.dropboxusercontent.com/u/60558749/Copy%20of%20Sandusky%20Dental%20Care%20practice%20monitor%20kims%20working%20copy%20%28for%20lreaide%20at%20chandoo.org%29.xls


I did this:

a) I've taken sheets of actual dates (2012-2013 for Sandusky and 'Lex 2013' for Lexington).

b) I added the actual date in cell A1 (if you want always today's date it can be easily fixed replacing that value for =TODAY().

c) I modified column A to change strings representing dates Jan'12 to actual dates with these formulas:

Last year: =FECHA(AÑO($A$1)-1;FILA()-1;0) -----> in english: =DATE(YEAR($A$1)-1,ROW()-1,0)

Current year: =FECHA(AÑO($A$1);FILA()-1;0) -----> in english: =DATE(YEAR($A$1),ROW()-1,0)

d) I removed the manual highlighting for ranges $3:$14 and $21:$32

e) I set two rules for conditional formatting for those ranges as:

$3:$14, green: =$A3=FECHA(AÑO($A$1)-1;MES($A$1);DIA($A$1)) -----> in english: =$A3=DATE(YEAR($A$1)-1,MONTH($A$1),DAY($A$1))

$21:$32, orange: =$A21=$A$1

f) Almost the same for Sandusky from b) to e) described for Lexington.

g) In Sandusky for the consolidation:

- A38: =FECHA(AÑO($A$1)-1;MES($A$1);DIA($A$1)) -----> in english: =DATE(YEAR($A$1)-1,MONTH($A$1),DAY($A$1))

- A39: =FECHA(AÑO($A$1);MES($A$1);DIA($A$1)) -----> in english: =DATE(YEAR($A$1),MONTH($A$1),DAY($A$1))

- B38:C38 : =INDICE($3:$14;MES($A$1);COLUMNA())+INDICE('[<Lexington file>]Lex 2013'!$3:$14;MES($A$1);COLUMNA()) -----> in english: =INDEX($3:$14,MONTH($A$1),COLUMN())+INDEX('[<Lexington file>]Lex 2013'!$3:$14,MONTH($A$1),COLUMN())

- B39:C39 : =INDICE($19:$30;MES($A$1);COLUMNA())+INDICE('[<Lexington file>]Lex 2013'!$21:$32;MES($A$1);COLUMNA()) -----> in english: =INDEX($19:$30,MONTH($A$1),COLUMN())+INDEX('[<Lexington file>]Lex 2013'!$21:$32,MONTH($A$1),COLUMN())

The same procedure for B38:B39 should be replicated all across the related columns wanted (as I see that many of them are zero).


This is just a sample so as to check if it's what you were looking for. If it is, here are my recommendations:

1) Standardize the names of the correspondent worksheets in each workbook (better if the same instead or prefixes like "Lex ").

2) Standardize the rows structure of the common parts (Lex ranges are 3:14 & 21:32 and Sand 3:14 & 19:30), so as to simplify formulas.

3) I didn't checked anything else than described, but the idea is to have workbooks with worksheets as much as homogenous as possible to make things easier. This doesn't imply number of columns, if they're additional (BP in Lex vx. BW in Sand); if not, just make them equal and hide those you don't want. Additionally the only part added to Sand should be the summary of the consolidation.


Just advise if any issue.


Regards!
 
OMG! I will have to look at what you did when I get home tonight but I had no idea it would be that complex - I thought I had done pretty good with my very limited knowledge of Excel but what you did - I don't know what to say....you are awesome! I can't wait to check out your work....You may have just made my work load soooo much easier. Thank you, and like I said, I can't wait to check this out!


Thank you, thank you!


Kim Courtney
 
Hi, lreaide!

Thanks for your workds, but about that of "I can't wait to check out your work"... the balloon is in your side of the field now, so it's me who can't (in fact, I actually could!) wait to check out your work. :)

That's to say, if you decide to standardize both workbooks and define proper names for worksheets and you build an unique model for Lex & Sand (except the consolidation footer), then I'll be able to help you further with more easiness and safety regarding future modifications.

Regards!
 
You must log in or register to reply here.
Back
Top