+ Reply to Thread
Results 1 to 3 of 3

Row calculation changes each month when data from previous month is deleted

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Row calculation changes each month when data from previous month is deleted

    Excel 2007

    I volunteer for a food pantry and am trying to us the data we collect each month to automatically generate the numbers for the reports we are required to submit to the Food Bank in order to keep our association with them.

    I have 12 sheets in the workbook, one for each month. All the formulas work correctly and generate the numbers. I have a 13th sheet with 12 report "blocks" on it and a 14th sheet with vlookup data on it. As the client data is entered the numbers change at the top of the monthly sheet and also on the report page in the corresponding monthly block. So far, so good, the numbers for the reports are generated.

    Originally I had it calculating the numbers from rows 6 through 2000 in each column.

    The problem that has risen now is at the the end of each month, the ladies, in an effort to keep from typing so much of the same data each month would like to keep the previous month data on the new month sheet. Most of the time for a returning client just the date is changed to the corresponding month.

    For example, lets say you enter that data for January, its no problem. If you copy the sheet and rename it February and add the data for new clients for the month of February, you then have both months data on one sheet which is still not a problem, as you sort by date and delete the January data to give you the February report. Before the January data is deleted from the February sheet it is then and renamed March. Now you have 2 months of data on March sheet.

    To keep it manageable you sort by date and then delete the January data on the March sheet and that's where the problem comes in. When the January data is deleted from the March sheet, the cells that are calculated are reduced by the number of rows deleted. Originally it was calculating rows 6 thru 2000 but if you delete 500 rows of January data from the March sheet the formula changes to only calculating rows 6 thru 1500. Each month the numbers are reduced at some point it will quit calculating unless I go in and redo the formula to it's original state.

    Is there a way to keep the number of rows constant in the calculation even when you delete some on the next moths sheet? In other words, keep rows 6 - 2000 or would it just be easier to increase the number of rows in the calculation to 20,000 or 30,000 so the formula doesn't run out as they delete?

    I've tried various ways but haven't come up a more "elegant" solution.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)

    Re: Row calculation changes each month when data from previous month is deleted

    If you set your data up as tables, you should be able to avoid the problem.

    Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI

    Re: Row calculation changes each month when data from previous month is deleted

    I can suggest PivotTable but without seeing workbook that's all what I can say

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  2. VBA code adjustment to include only current month and previous month automatically.
    By ElmerFud in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-05-2015, 03:02 PM
  3. Replies: 1
    Last Post: 07-06-2015, 04:43 PM
  4. Macro using vlookups comparing 2 months paysheet(previous month and current month)
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-07-2012, 03:45 AM
  5. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  6. [SOLVED] VBA to identify the current month and previous month based on system date
    By ravikumar00008 in forum Excel General
    Replies: 10
    Last Post: 07-26-2012, 10:04 AM
  7. Replies: 4
    Last Post: 03-19-2012, 08:58 AM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1