Forum Discussion
Extracting date
I attached an example, I am trying to populate information for about 200 accounts (arranged horizontally in the sheet MasterList). I have a list of about 45 items to populate (the example has 5 items). I want to automatically populate individual sheets like the two sheets in the example for all 200 accounts.
Thanks for your input!
Bear with me please.
You haven't explained what you're going to do with those individual sheets.
So I'm still going to push to have two basic databases:
- one which is the account numbers and individual data corresponding to each account (you'll see I reorganized yours)
- the other being--Well, here's where I was asking you what the larger context is--for the time being I'm going to assume it would contain such things as transaction history, deposits, withdrawals, whatever; it could also contain actions/transactions of other types..essentially a history of the accounts in your custody. I haven't done anything to mock up this, because I wanted to find out what it is you anticipate doing with the individual sheets. FYI, it wold be very easy using the new FILTER function to list any accounts deposits and withdrawals (and other transactions) on the page, extracting them from a comprehensive list of all clients' account histories, putting them in date (or other category) order.......
My point is that from those two databases (which are all encompassing) you could produce individual sheets on an ad hoc basis.
So I've illustrated that with the example data you've given me. What I'd like you to do with this is play around a bit, seeing how you can add new accounts and have the drop down list on the "Indiv" tab automatically include those new account numbers and therefore easily be populated by the additional data. [I didn't provide room for the second street address, but that's easily handled]
Instead of current account value I created a field called Opening Account Value or something to that effect. Current value is a volatile number, and would be produced in that ad hoc page, based on transaction history (assuming that's what you want).
But please tell me what it is you are going to want those individual pages to show, and then we can talk about whether this kind of design will work.
A caveat: that data validation list that is used to populate the options in the drop down box makes use of the UNIQUE and SORT functions which are only available in the most recent release of Excel, so if they're not working for you, then we'll have to do something else...(or get you that newest Excel)
My hope was to create individual forms per account in order to keep them in my client folders. The Master List is related to a regulatory requirement. I will have to discuss an overview of the data with the regulator and be prepared to discuss randomly sampled individual cases.
The Indiv sheet that you created looked pretty good. When I finish designing the report, I expect to have at least 25 data points for each of the 200 accounts. I will play around with it and see how it goes. As mentioned in my original question, I (well, my colleagues) prefer accounts organized by columns and categories in the rows. But, I am comfortable with the transposed view. It looks fine to me.
Thank you very much for you help.
OK. I was trying to help with an intro as to how treating your data as a table (aka database) could work. And I was differentiating between that the raw data storage areas (which serves as the input), and what you're now calling a "view," which I would consider "output"....
One of the classical wrong ways (IMHO) to approach designing an Excel workbook is to confuse the two, to think of Excel mostly in terms of what you're expecting things to look like at the output end, planning to enter data that way. This may not be what you're doing, although that "master" layout kind of looks like it. My revamping of it was never intended as a "view"-- solely as a database, from which views (output) would be constructed.
If what you're looking for is 200 (printed?) sheets of basically static info, one for each client folder, then Excel is one way to do it; Word might work too. If, on the other hand, there's a lot of action with those data points, especially if you want to retain history of various events in each account, then I'd suggest you try to differentiate between the data to be stored (and learn about Excel Tables), and then let us come up with ways to extract the data into useful reports, or client snapshots, more dynamically. Doing that is using Excel at its best. Using it primarily as a way to organize data into rows and columns (conceiving it as sort of a semi-automated ledger sheet) just scratches the surface of what Excel can do.
I recognize that may not be what you're doing. But so far, it sounds like it, if only because you have been speaking of "at least 25 data points" but not of a process or series of transactions. It's just (it sounds like) static data, occasionally changing to be sure, but not really tracking transactions for those accounts.... There's nothing wrong with that; it's just that you wouldn't be taking full advantage of what Excel can do.
Thanks for you comments and clarifications. You are correct that it is mostly static data. Storing useful data and extracting data for useful reports or client snapshots is an accurate description of what I am trying to do.
This is the first time I have sought help on a board. I am blown away by helpful people are!
- Forgot to add that I do have the laterst version of Excel.
A postscript to our exchanges of yesterday. If it's really the case that all you want to do is take records that are organized in columns and take data for single clients and put it on a single sheet for including in a client's folder, so long as the columns are all consistently organized and the output you want is consistently laid out, you could set up a series of XLOOKUP or HLOOKUP functions to do that. (By series, I think it could be a single formula copied to multiple cells, but it's hard to say without seeing the actual source and end result desired). It shouldn't be necessary to write VBA code.
Would it be possible for you to post a complete example, not just the small subset, and do so without any real names and addresses just as you did with the small sample.
It should be noted, too, that if this is really all you are aiming to do--which is legitimate--another solution which might work and give even more flexibility is to use the Excel data as the source for a mail merge document in Word. With that your final layout could have even more "zip"--to the extent that's important. In that case, though, you'd really want the source data to be arrayed in rows, as an Excel Table.