Can I autofill cells using percentages from another spreadsheet?

Baldeagle · #1 11-22-2014, 03:55 AM

I'm doing voluntary work for a small charity. We have a list of about 50 donors (with alpha/numeric IDs). They can donate money and spread it between 5 projects by indicating a percentage for each. This information is held in one spreadsheet.
When donations are received they are entered into the Ledger with the date, amount and the donor's ID. Is there any way to automatically populate the following 5 cells (one for each of the projects) with the appropriate amount calculated by reference to the donor's ID and his specified percentage for each project?

I'm not a VB expert so if coding is required I would appreciate some specific guidance.
Thanking you in anticipation.

gmayor · #2 11-23-2014, 04:33 AM

That should certainly be possible, perhaps on the lines of the attached.
Save the two attached files then open the file Ledger.xlsm and change the code associated with the button to reflect the path where where you have stored the two files. It should give you food for thought.

Baldeagle · #3 11-23-2014, 08:41 AM

Thanks very much, Graham, for your response and suggestion. That has been very helpful.

But because we would normally be collecting information about new donations from Bank statements downloaded from the internet, we would be populating the first 3 columns in your Ledger.xlsm from that source, rather than manually entering each donation as it is received. Is it possible to adapt what you have created to fill the 'project' columns automatically once data is entered into the first 3 columns? That would achieve what we would be looking for.

I look forward to hearing from you again.

gmayor · #4 11-23-2014, 08:51 AM

I don't see that being much of a problem. Does your 'donors' file look like the test one? Can you post copies of your workbooks (with the header rows, but either no data or a couple of lines of dummy data)? Use Go Advanced to attach them.

If no one picks it up over night I will look at it again tomorrow.

Baldeagle · #5 11-23-2014, 10:00 AM

Thanks for the follow-up.

I am attaching 3 files with made-up data - 'Bank Transactions.xlsx' which contains the raw data downloaded from the Bank, 'Donors.xlsx' which has the information about new Donors (and their instructions for sharing their donations among each of the 5 projects) and 'Ledger.xlsx' which contains the information for all gifts from the Bank and where we want to populate the 'Project' fields with the appropriate share of their donation.

I trust this makes sense and will help you to help me!!

gmayor · #6 11-24-2014, 05:03 AM

OK I think the following will work for you - I hope so, because it took rather more time than I had intended.

The attached workbook 'Process.xlsm' contains all the code required to process your bank statements (provided they are in the form of your sample). Open the template (you can save it as an add-in if you wish) and you will find a button on the Add-ins tab of the ribbon which has two options.

One will import your bank statement into the ledger, with the appropriate percentages logged. The details regarding the three workbooks and their associated worksheets are stored in the registry and restored next time the function is used. You only need to select the next bank statement for processing. I have allowed the process to reload the bank statement next time, with the assumption that you might use the same file each time but with different contents. If that is not the case you can modify the code so that it doesn't recall the bank statement file.

The other button will clear the settings from the registry.

If you already have a large number of entries in the ledger (as the code assumes a new ledger) then you can adapt the same core function to grab the percentages, but I haven't done that for you).

I have not corrected for a bank statement that contains a name that is not in the Donor's list.

Baldeagle · #7 11-24-2014, 05:41 AM

Thank you very much for taking the time to deal with my question - I really appreciate your assistance.

I have just had a quick look at what you have sent me and I think it will take me some time to get my head round the intricacies of what you have produced!! This is a very different world for me but I will explore this and will try it out with my data. I will let you know how I get on.

But again, very many thanks for all you have done - the charity will, I am sure, benefit from your work.

gmayor · #8 11-24-2014, 06:21 AM

Nothing really to get your head around. Open the process workbook. Click the button on the Add-Ins tab and follow the instructions. Test with the workbooks you provided.

Baldeagle · #9 11-24-2014, 09:39 AM

Sorry - when I posted my initial reply I hadn't, at that stage, discovered how your process actually worked!!

I have now tested it and am very impressed. If it wouldn't take much more of your time could I ask two follow-up questions?
1. Would it be easy to adapt the coding to simply put the information from the Bank Transactions file into the Ledger (without attempting to fill the 5 Project cells) if the donor's information wasn't already in the Donors file? I got an error when it came to a new credit which hadn't been identified in the Donors file in advance. I would prefer in such a situation to populate the first 3 columns in the Ledger only (rather than stop the process with an error message).
2. If at some future date we were to change the layout of the Ledger (with additional columns inserted) where would I go looking for the piece of coding that I could adapt to find the right columns?

I really appreciate the work that you have put into this - it will certainly improve the ease with which the charity can handle their monthly recording of credits.

gmayor · #10 11-24-2014, 10:04 AM

1. I was afraid of that - and did warn about it. However it shouldn't be too difficult to trap. I'll look at that tomorrow.
2. The code uses an array to hold the Donors file. You will find the cell allocations in ModMain.

Baldeagle · #11 11-24-2014, 12:42 PM

Just another 'stupid' question!!

If I wanted to run the Add-in from another file how do I copy it and get the Add-in tab to appear?

gmayor · #12 11-24-2014, 10:12 PM

If you save it as an add-in XLAM format then install it as an add-in the ribbon tab will always show, however there is no need to do that. It will work with any workbooks that have the correct format. Open the workbook. Click the Reset button and add the workbooks from the dialog.

The attached corrects for donors not in the donors' list.

Baldeagle · #13 11-25-2014, 05:57 AM

Thanks for making that change for me - it works perfectly. And I greatly appreciate the work that you have put into this for me.

I have however tried to use it to draw data from a different file - 'Income Analysis.xlsx' but the Registry box didn't provide me with all the different 'sheet' options. I discovered that it just listed the sheets where the sheet name had only one word. Mine was called 'Bank Credits'. Is that something that is built into the coding somewhere? Or can it be altered to show all sheets including that that have a space in the name? Or maybe that's bad designing on my part?

Baldeagle · #14 11-25-2014, 10:04 AM

Just one further (final?) question - is it possible to slightly modify your coding to ask for the start and end date of the data being selected? For another purpose we might want to select all credits received during the previous month.

gmayor · #15 11-26-2014, 01:48 AM

I'll have a look when I get a minute, but I am recovering from a lightning strike on the house that took out some electrical systems, so it might take a few days until I get back to speed.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Autofill Address	dazwm	Outlook	13	08-21-2013 09:28 AM
Autofill Automation	Parkiee1981	Excel	4	03-15-2013 01:24 PM
How to calculate percentages???	fabthi	Excel	3	04-13-2012 12:08 PM
Charting percentages within a percent	Tom0822	Excel	0	03-05-2012 08:50 PM
Need cells to autofill with color	Silver Rain 007	Excel	1	10-22-2009 08:19 PM