Populate cells using dynamic named range, VBA

[Paradigm_Shift]

I have a sheet containing 2 lists of categories for income and expenses. These lists are named inc and exp, respectively, and are contained in a sheet called "Lists." They are dynamic ranges, meaning every time a value is added, the named range adjusts itself to include the new value(s). [=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)]

I have 2 tables summarizing 12 months of data. The tables use sumif to find all occurrences of each category and sum them. There's 1 table for income, 1 for expenses. Each is a 2 column table, with all the categories for income in column A and their total for 12 months in column B. Same for Expenses in D & E.

To populate the categories in column A & D, I am currently using the simple =Lists!A1 =Lists!A2 (inc range), =Lists!B1 =Lists!B2 (exp range) and so on... the problem is when a value is added to either list, while the named range adjusts, I have to manually drag the formulas in Column A & B down 1 more cell to include the new value.

How can I use VBA to look for the inc and exp ranges (which will change in size), then populate each table with the most recent categories?

Thanks in advance for responses.

 

[Paradigm_Shift]

OHHH, ok. I'm sorry. I thought it would trigger when I changed (switched) worksheets. My bad. Noob.

So I just changed the range selected from A:A to A1:A5000 b/c there will NEVER be more than that. Cop out workaround, but it takes care of the issue with the 3 extra rows...

Anyway, I added a value to the income list, hit enter and it copied the updated list to my summary sheet...however, when i added an item to my expenses list and hit enter, nothing...any idea?

p.s. then i went back to the income list and added another item and hit enter. voila, it then captured the change in the expenses column (of course.)

 

[Paradigm_Shift]

nevermind, i'm an idiot. i forgot to change the A:B Range to A:D since columns B, C and D all contain expense categories I want copied...i was making changes in column D which wasn't in the target range.

Thank you so much for your help!

 

[Jerry Sullivan]

Check to make sure your exp dynamic range is in Column B of your Lists Worksheet (I notice your references on your summary sheet shifted from Cols A, D on your first post to B, E on later posts).
The code example I posted ignores changes to cells outside of Lists!A:B

 

[Paradigm_Shift]

I probably confused you with all these column references...inc is in column A on the sheet "Lists" and exp is in column B:D on the sheet "Lists" (I took care of the 3 column thing no problem. slight alteration of your code).

The 2 tables I needed the data copied into are actually columns B:C for income categories and total, and columns E:F for expense categories and total...I just said A and D in the original post to keep it simple...guess I made it a bit more confusing. ha!

Thanks for the help!

 

[Jerry Sullivan]

Glad to have helped. Cheers!