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: https://www.ozgrid.com/forum/member/...33#post1196081

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.
Link

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?

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

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

Hi,

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.
Thanks!

Re: Sum multiple sheets with 'sumif' criteria

forum.ozgrid.com/index.php?attachment/66501/

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.

Hi,

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:

{=SUMPRODUCT((IF(ISERROR(MONTH(sheet1!$A:$A)),sheet1!$A:$A,MONTH(sheet1!$A:$A))=MONTH(sheet4!C$1))*(IF(ISERROR(YEAR(sheet1!$A:$A)),sheet1!$A:$A,YEAR(sheet1!$A:$A))=YEAR(sheet4!C$1))*(sheet1!$C:$C=sheet4!$A3),sheet1!$B:$B)}

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

{=SUMPRODUCT((IF(ISERROR(MONTH('sheet1:sheet3'!$A:$A)),'sheet1:sheet3'!$A:$A,MONTH('sheet1:sheet3'!$A:$A))=MONTH(sheet4'!C$1))*(IF(ISERROR(YEAR('sheet1:sheet3'!$A:$A)),'sheet1:sheet3'!$A:$A,YEAR('sheet1:sheet3'!$A:$A))=YEAR(sheet4!C$1))*('sheet1:sheet3'!$C:$C=sheet4!$A3),'sheet1:sheet3'!$B:$B)}

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!

Re: Identify if specific formula is used in range

Hi MrRedli

That code worked great! Thank you for the quick advice.

-Oldman

Hi,

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.

Thanks

Re: VBA search in tabs listed in excel column

Hey cytop

Your code is working great. Thank you very much for your help

Re: VBA search in tabs listed in excel column

Thanks cytop!

I'll give it a shot and see how it fits into what I'm trying. I'll post how it goes. I definitely appreciate the help

Hi

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??

I think you're onto something exceleb. I wasn't thinking about that method at all, but it's simple and it works so I like it a lot. Thanks for the help. I greatly appreciate it

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

Those are both a lot cleaner than what I came up with.

Thanks royUK and Rajivalwar. Your help is very much appreciated!

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

Sub ProtectWorkbook()
     
     
    Dim wrkSheet As Worksheet
     
    For Each wrkSheet In ActiveWorkbook.Worksheets
         
         If wrkSheet.Name Like "Inputs" Then
            GoTo 0
        End If
        
        
        wrkSheet.Protect ("password")
            
    Next wrkSheet
     
0
End Sub

Hi

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:

Sub ProtectWorkbook()
     
    Dim wrkSheet As Worksheet
     
    For Each wrkSheet In ActiveWorkbook.Worksheets
         
         If wrkSheet.Name = "Input" Then
            End Sub
        
         Else




        wrkSheet.Protect Password:=”password”
            
    Next wrkSheet
     
     
End Sub

Thanks for the help

Re: Protecting Workbook, leaving highlighted cells unlocked

I've found something that seems to work:

Sub ProtectWorksheet()




Dim rng As Range




    For i = 1 To Sheets.Count
            
            For Each rng In Sheets(i).Range("A1:Z200")
                If rng.Interior.Color = RGB(204, 255, 204) Then
                    rng.Locked = False
                End If
            
            Next rng
         Sheets(i).Protect Password:="password"
      Next i




End Sub

Hi

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

Sub ProtectWorkbook()




Dim wrksheet As Worksheet
Dim rng As Range
    
    For Each wrksheet In ActiveWorkbook.Worksheets
        
        For Each rng In Range("A:AAA")
            If rng.Interior.Color = RGB(204, 255, 204) Then
                rng.Locked = False
            End If
         Next rng


        wrksheet.Protect Password:=”password”
    Next wrksheet




End Sub

Thanks

Re: Search multicolumn/row array and offset function

Great thanks dbrown