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

VBA Code to Dynamically Expand Table if Another Table Expands

J

JenniferS

Member
Hello,

Is it possible to expand the table on the Notes sheet if the table on the Transactions sheet changes?

The data on the Notes sheet rows must stay with the Transaction IDs. Please see attached for more information. I need the ability for the Category and Subcategory to be user selectable from the drop downs and notes added as needed.

Thank you very much for your help
 

Attachments

  • Dynamic Table.xlsm
    539.6 KB · Views: 8
Nope, nothing wrong. Your request, as written, is just a bit more complex.

That said, I would argue that your plan is a bad plan. There's no reason to completely duplicate data from one sheet to another. Instead, just add the Notes field to the existing table in Transactions sheet. That way you're guaranteed to keep all the information together on a row by row basis, as well as having the field expand as more data is added.

If this is a budgeting tool, note that there are hundreds of free templates you could use or examine for inspiration.
 
The first sheet gets it's information from external code that is not in my control. This is why I cannot alter the first sheet rather update the data on the second sheet and add notes as needed.
 
JenniferS
One possible sample.

Notes:
Your file uses some Add-Ins ... I didn't accept/noted that all.
I didn't check Your codes at all.
If You change something from Transaction-sheet then changes should refresh Notes-sheet .. as well as save previous value.
Did You mean that if Your get more rows to Transaction-sheet - that would be 'change' too?
... There is an option for that - if You don't need it - then take that option away.
Expand means that new/changed data should try to take care.
I didn't check that if You delete some rows - how will this work.
 

Attachments

  • Dynamic Table.xlsm
    541 KB · Views: 4
JenniferS
You seems to miss one set of my code at all which You've asked and
You seems to use that plan b-option, which don't work as You've asked.
 
Last edited:
As I wrote, You've missed the main part of my code (based Your sent and deleted reply).
You someway use only that plan b-option.
Test my file again.
It should update then You'll select Notes-sheet
- before that, You don't need those updates - or how?
As well as reread my blue writing.
 
Thank you I will continue to test. The Transactions sheet is updated externally and I do not have any control over it. So with your code I now have the ability to identify the Category, Subcategory, and notes.
 
No it is not working. When I ran the code this time it replaces the Category and Subcategory with the ones from the Transactions sheet. I need the Category and Subcategory that I select to remain on the Notes sheet please.

The Category and Subcategory on the Transactions sheet are often wrong is why I need your code to have correct Category and Subcategory on the Notes sheet.

Thank you
 

Attachments

  • Money in Excel BOB 1.1.xlsb
    560.9 KB · Views: 4
JenniferS
I works as You're using it.
Now, You're still using that part of code,
which should use only once after get new data to Transactions-sheet.

You do not use that main code (from Transactions-sheet), which You've asked.
Your Is it possible to expand the table on the Notes sheet if the table on the Transactions sheet changes?
 
After a short work,
I got an image that
You've asked something
and
You would like to get something else.
Your original was
... if something changes with Transactions-sheet
... for me, that means, if any cell's value will change then refresh Notes-sheets same cell.

Now, do You want that
- if You modify something in Notes-sheet, those cells won't change anymore based Transactions-sheet?
- if Transactions-sheet get new data, then those datas would paste to Notes-sheet too?
... or what?
 
Yes new transactions should be posted to Notes sheet but with me able to select category, subcategory and notes for the new transactions.

In fact if I change a category, subcategory, or note (on the Notes sheet) these should not change after running the code.
 
You do not need to run the code.
The code will check if it needs to run - as I've tried to offer for You.
This version would be more close with Your newer writings.
If same number of rows ... nothing will happen.
If different number of rows ... new rows would be pasted and sorted.
 

Attachments

  • Money in Excel BOB 1.1.xlsb
    561.3 KB · Views: 3
Hello,

I did an update of transactions and now the total row counts are different. I am not sure if there are duplicates or why there are more rows on the Notes sheet.

Can you please take a look?

Thank you
 

Attachments

  • Money in Excel BOB 1.5.xlsb
    535.8 KB · Views: 1
I could look ...
#1 Seems You didn't use my the latest version ... I'm using it.
#2 There seems to be more serious challenge
Transaction ID seems to be different in Transactions- and Notes-sheets ...hmm?
If those will change ... then there will be a lot of new rows of data.
Screenshot 2021-10-23 at 16.46.23.png
Screenshot 2021-10-23 at 16.48.04.png
#3 There were challenges with dates ... some were mm/dd/yyyy ... this version should take care that (if Transaction-sheet dates are real dates)
 

Attachments

  • Money in Excel BOB 1.1.xlsb
    571.4 KB · Views: 3
I had to re-sync with banks and they assigned different transaction IDs.

I used the same workbook and made a new sheet. In Sheet1 I have unique transaction IDs between Transactions and Notes. At this point I just need to find where these unique IDs are and assign the appropriate Category and Subcategory.
 

Attachments

  • Money in Excel BOB 1.5 Unique IDs.xlsb
    684.4 KB · Views: 4
You must log in or register to reply here.
Back
Top