Forum Discussion
Extracting date
First off, storing data records in rows is the far more common way, and entirely suited to Pivot Tables. And then you can convert the range of data to an Excel Table and far more easily do other things as well--e.g., take advantage of the newer Dynamic Array functions SORT, UNIQUE, FILTER...
So let me first disabuse you of the notion that having your client account information in columns is suitable. They may work for Pivot Tables, because of the flexibility of Pivot Tables, but they'll end up limiting you more than helping you. Seriously. Check out any texts on data bases and tables in Excel; I doubt you'll find a one that advocates doing the records as columns.
Second, could you say more about what you'll be storing in those individual sheets, one per client, and maybe a word or two about how many clients you're talking about.
It's clearly a database of some kind that you're building. The question is what would be the best database design to accomplish your purposes. Excel is really VERY good at taking a single table and producing a summary report. The Pivot Table, with its cross-tabulated summary of monthly transactions per client (for example) is something you're already familiar with. It's also possible that you'd be better off using a single table for all transactions, keyed (obviously) to each client's account number(s), but still a single table for all of them, and then let Excel do it's trick of separating out the relevant transactions (or whatever) per client......i.e., let Excel do the "heavy lifting" of extracting data based on a few parameters, rather than having to search for each client's separate sheet every time you want to enter something pertinent.
I'm not saying that's a foregone conclusion, but I do want to make sure you've considered the possibility rather than assuming that, because you'll want OUTPUT that is client specific, that means you also need to have the INPUT be on client specific sheets. That premise is not a necessary (or, for that matter, helpful) way to approach your design.
So, if you can forgive the impertinence of my asking you to explain your business goals here rather than just helping you do what you asked, my goal (and I think that of others on this site, many of whom are far more expert with such things as Power Query than I) is to help you come up with the best design, and to do that we need to ask for a description of the bigger context within which you're operating.
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.