excel question - pulling dynamic values from same cell across multiple workbooks

[mitchafi]

Hi all,

I work for a nonprofit social services organization that provides monthly stipends to clients who are unemployed but actively job searching. I have several payment schedules, each named "Last name, First name.xls" and located in one folder. These are all identical in format and one cell in each workbook (let's say F10) contains the total amount of money disbursed to the client.

Now, I have another master spreadsheet with the names of all of the files in rows A1-A50 (i.e. Last name, First name.xls) which are being pulled automatically from the folder using the name manager/index function.

What I need to do is write a formula/function to populate the master spreadsheet with the numerical values in F10 across all of the workbooks in the folder. Then, as clients receive additional money and the value of F10 changes, the master spreadsheet will automatically pull the new value.

The formula I'm using now is:

=(CONCATENATE(A2, "[", A3,"]","IRC Copy!f10"))

This formula pulls together 1) the path of the folder 2) the name of the individual file (last name, first name.xls), and 3) the name of worksheet and the cell containing the total $ spent for a given client. It produces something that looks like this:

\\OAKFPS02\Data\RESETTLEMENT\Employment\Match Grant Enrollment Forms\6. MG PAYMENT SCHEDULES\FY 2018\Active\[Last, First- Payment schedule.xlsx]IRC Copy!b37

This is where I'm stuck. That formula is producing the correct filepath, I believe, but I don't know how to get Excel to now put that VALUE in cell B2.

Any feedback would be greatly, greatly appreciated. Thanks in advance!

 

[PowerEngineer]

Excel as a function called INDIRECT which can be used to turn a text string (like the one you are building) into a formula that puts the value of executed formula in the cell. I suspect, however, that you will not like the limitation on INDIRECT that requires that any referenced Excel spreadsheet needs to be open for it to work. You may be able to get around this by changing the way you reference the cell in the individual spreadsheets by using the INDEX function - something like INDEX('E:\Excel file\[test.xls]sheetname'!F:F,37,1); I haven't actually tried this myself.

 

[Mothergoose729]

Not really the answer you are looking for, but as a software developer I am always surprised to what lengths people will go to to get excel to do what a database really aught to be doing. This kind of stuff is trivial in access or another DB client. It is probably easier to learn how to use better tools than to get excel to bend itself in pretzels to do it instead.

I don't know what your background is or how much time you have to devote to this thing. If it were me, I would start with at least a client and a payments table, and that master data sheet would probably be replaced with a query or a view. If you need to automatically update data based on inputs you can do that with triggers, but usually that isn't necessary if you have a sensible schema. There are many methods for importing excel data into a database... access can do much of it for you auto-magically.

You will probably stick with excel because that is what you know, and that's fine. A lot of people (some how) manage to get by with it, but it really isn't a tool that should be used for managing data of any real complexity.

Just some food for thought 🙂.

 

[mitchafi]

Thank you both for your feedback! Mothergoose: haven't used Access for 5+ years and never used it much to begin with, but I am certainly open to it, and it definitely seems like it would be useful for operating this program more efficiently.

So here is where I am at: I've got my database with two tables, one for clients that includes case number, name, case size, enrollment date, exit date, etc.

I also have a payments table that has case number (I guess this would be the common denominator for all tables), payment type (i.e. rent, transportation, etc), payment amount, check #, request date, distribution date, etc.

Now, all of the client data is currently kept in individual spreadsheets, 1 per case/family. For example, case number is in B9, enrollment date is in F7, exit date is in B35, payment type is in A14-30, and payment amounts are in B14-30. Is there a way that I can pull this data into the database automatically?

Even better would be to be able to link each individual excel workbook to the database so that if a caseworker went in and added a new payment to the spreadsheet, or changed a payment amount, it would get updated in the database automatically. The caseworkers can be resistant to change, and allowing them to keep using excel would be easier than forcing them to start using access.

Additionally, we have to have one individualized payment schedule that we can quickly print off and go over with clients so that they understand when they will be receiving payments, for how much, etc.

Any help would be appreciated, but even just a few pointers in the right direction would be great!

Best,
Mitch

 

[thecoolnessrune]

Yes, I'd recommend reading up on some of this link: https://support.office.com/en-us/ar...d-access-409c27cc-c69d-461d-a74c-35392a68ed53

Access and Excel can almost fully interact with each other. You can link cells in Excel spreadsheets to Access Database Tables to have them populated, but when you do that you can only update the values from the individual Excel file.

Payment Schedules are pretty easy in Access by generating on-the-fly reports. Query Language makes it very easy to get the grasp of. There's a good article on getting started with relationship management here: https://support.office.com/en-us/ar...e-tables-4a14e73a-3ecd-481c-a416-09571c7064a9

The primary points are, take time to break down how you want Access to work for you. From your OP, you're wanting to work with *relational* data entities (they relate to each other in some way). Using things like the Relationship Wizard to explain to Access how your data is structured will help it do what you want.

For instance, you'll probably want a ID Number as a unique value for each person. But you'd specify that each case number can have multiple people, but that each person only has one Case number (though maybe not! you gotta think about that). That could be a 1:Many or a Many:Many relationship. Things like Check# would be a 1:Many Relationship as well (A Case Number could have many checks, but a check will only belong to one case number). Payment Types will have a Many:Many relationship with people, because a person could have have many payment types, and a payment type could be used by many people.

Basic Table Normalization is probably the first big hurdle people have to deal with when dealing with Databases, but getting your feet under you there and getting the database developed right the first time saves you hassle later. There's a decent article on the concepts of Normalization here: https://support.office.com/en-us/article/create-a-relationship-40c998dd-4875-4da4-98c8-8ac8f109b85b

 

[Tweak155]

I did VBA development for a number of years exclusively, and still do it for side jobs. What you want excel to do in this case is fairly trivial, but I agree a database is a better solution for this.

That said, if ALL you need to do is pull in these F10 ranges into one single spreadsheet, I can write you a simple macro to do this. But if you want to keep expanding functionality, I do think moving this information to an Access database will be a better first move.

Let me know if the VBA macro would solve all your problems 🙂

EDIT:

Since this was fairly simple I just put something together to see if you like it.

Based on your loose description, you can try this macro. My assumption is that A2 is a path, A3 is the workbook name, and sheet name is "IRC Copy", and this pattern repeats A4 & A5, A6 & A7, etc. This will write F10 into B2 for A2 & A3, into B4 for A4 & A5, etc.

To use this, open the master file and hold ALT and press F11, in the left hand pane of the new window that opened, double click the sheet name where this data will be imported (where the A2 & A3 cells that you described), paste this code in. Now go back to the master sheet and double click in cell A1... some magic should happen!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count = 1 And Not Intersect(Target, Range("A1")) Is Nothing Then
    Dim rng As Range, wb As Workbook, ws As Worksheet, strPath As String
   
    For Each rng In Columns("A").SpecialCells(xlCellTypeConstants)
        If InStr(rng.Value, "\") > 0 Then
            strPath = rng.Value & rng.Offset(1, 0).Value
            Set wb = Application.Workbooks.Open(strPath, , True)
            Set ws = wb.Sheets("IRC Copy")
           
            rng.Offset(0, 1).Value = ws.Range("F10").Value
           
            Set ws = Nothing
            Application.DisplayAlerts = False
            wb.Close False
            Application.DisplayAlerts = True
            Set wb = Nothing
        End If
    Next rng
End If
End Sub