Posts by oldman33

    Thank you both for the input!

    Carim - the link was extremely helpful. It looks like fundamentally I am up against the limitations of multiple consolidation. I am now trying to learn Power Query and Union Query to see if either will fit my needs.

    KjBox - as noted above, I'm running into server errors trying to post an example workbook. I was able to post the example file on my profile page. Here is the link:

    I apologize for the crude workaround - I was able to post an example file attachment in my user profile (link). It should be the 1st visitor message in the 'activities' tab. This post is referenced in the message. Let me know if that doesn't work. Not sure why I keep getting different error messages while trying to post here.

    I've been trying to upload both an example file or screenshots but I've been getting the following error. Any ideas on how to get around this?

    Error while saving content: SyntaxError: JSON Parse error: Unexpected identifier "Fatal"

    Error while saving content: SyntaxError: JSON.parse: unexpected character at line 2 column 1 of the JSON data


    I am trying to create a pivot table from multiple worksheets. My problem is I can create a multi-source pivot table with date columns and $ amount values, but I am unable to populate the rows with different category options.

    Current Workbook Setup

    • 10 worksheets representing different spending accounts. All worksheets are formatted the same, with columns for date, $ amount, category
    • For each data entry, the user can select 1 of 50 categories. Categories are the same for all worksheets. **This category information is what I am having trouble creating in pivot
    • In total, each worksheet has 1,000+ entries

    Problem / Goal

    • I am trying to create a summary tab that will sum up the data from all 10 worksheets. I do no care about which data comes from which worksheet
    • I want a multi-source pivot table that is organized by month/yr in the column, by category in the row, and summed $ amount as the value
    • I can create a multi-source pivot table, but I can't get the row categories

    Deal Breakers

    • All account worksheets must remain separate sheets. I know combining into 1 sheet would solve my problem, but would create too many other problems

    I always appreciate your advice. The lag using sumproduct is killing my current file. If you have any other ideas, I'd love to hear them.

    Re: Sum multiple sheets with 'sumif' criteria

    This is a sample of what I'm trying to do. I now can't get either version of the previously stated formula to work correctly but I left them in as a placeholder. Let me know if you need more clarification. Thanks for taking a look.

    If this doesn't work, I'm trying to get multiple consolidation with pivot tables to work (haven't figured it out 100% yet). My thinking seems similar, just using formulas instead.


    I have 3+ sheets (say sheet1, sheet2, sheet3) all formatted the same way (with a header row):

    • Column A: Date
    • Column B: Amount
    • Column C: Category

    On a separate sheet (sheet4), I have a list of all the categories in Column A, and dates by Month/Year in row 1. I would like to sum the total amounts from sheets1-3 in sheet4 corresponding to the right month and year. I know I could sum several sumifs for each page, but I'm looking for something more efficient.

    I've tried the following sumproduct formula. It works summing just one sheet (sheet1) but returns #REF if I try multiple sheets:


    This is the formula that broke (same thing as above, but multi-sheet):


    So, any ideas on how to sum multiple pages when the criteria matches a category and month/year? I am trying to avoid helper columns in sheets1-3. They are pure imported data and I'd rather not make changes to those pages. And I would like to avoid summing each page (sheet1-3) 1 at a time because there's a potential to add several more similar pages. The shorter the formula the better.

    Any help is greatly appreciated. Thanks!


    I am making an input sheet where an end user will enter in several rows (roughly 20-30) of numerical data. The model I've built totals this data in cell b1 using =subtotal(9,b2:b30).

    Is there a formula that I can use that will search b2:b30 for the sum formula? My worry is that a user will create a subtotal row with sum, causing the model to double count. If I could use a formula that has true/false, count, etc that identifies if the sum formula has been used, I can make a warning messages to the user.

    I am using macros for this model so I am not opposed to using them to solve this. However, I would prefer to use a formula so that I can warn the user before the model is run.



    I have a sheet with a list of sheet names (some, but not all, of the other sheets in the workbook) in column A ('Sheet1' A:A). The list length can vary, but should usually be anywhere between 1-10 entries.

    What I would like to do is set a string variable in VBA, then have the code search the sheets that are listed in column A of Sheet1 for that variable.

    The basic logic that I am think of is something like:

    Dim FindThis As String
    FindThis = "Example"
    'Get all of the sheet names from 'Sheet1' A:A
    'Search tab1
    'if "Example" found, set sheet name and row as variables. If not then,
    'Search tab2 ....... and so on

    Thanks for all the help.

    Re: Match formula or similar function??

    That is a pretty good/simple solution that I may have to resort to. My only problem with that is I'm trying to track a fast moving draft so if I can just hit 1 key, it would be a lot easier for me to keep pace

    This is probably a simple Match type formula. I wouldn't be surprised if it was something else though...

    In sheet 1, I have a list of 80 items labeled 1-80 in column A with a header row. Columns B-G are labeled Group 1-Group 6 in Row 1. Each of the 80 items can only be selected by one group, and when it is, I will put an "x" or "1" in the corresponding row and column.

    In sheet 2, I have the same exact column A (the 80 items and header row). In Column B, I would like to use a formula that will search sheet 1, and if there is an "x"/"1" in one of the rows, it will display which group (using the header on sheet 1) selected the item.

    Thanks for the help

    Re: Macro to protect all sheets until certain sheet name

    And it always happens like this.... as soon as I posted the message I thought of the fix.

    Here's the code for anyone who needs it. Don't know if it's the best way but it works for me


    Need to create a macro that will password protect each tab in the order that they are arranged until the macro reaches a tab with a certain sheet name ("Input"). So everything before "Input" becomes password protected and everything after is not protected.

    This is what I have, but I'm struggling:

    Thanks for the help

    Re: Protecting Workbook, leaving highlighted cells unlocked

    I've found something that seems to work:


    I would like to make a macro that would search all cells within a sheet. If the cell background color is RGB(204, 255, 204), then the cell is unlocked. The macro would do this for all sheets within the macro. After every cell has been checked, the macro would lock all sheets (leaving the cells with the background color unlocked).

    Here's my attempt but I can't get it to work. I get a runtime error that says the sheet is locked (everything starts out unlocked but the first sheet does get locked). It only runs for one page when I want it to run for all sheets in the workbook. Lastly, it does not ask for a password when I unprotect the sheet after running the macro