Excel Auto-Populate?

The Crowing One

Hey all,

I have a quick question concerning Excel. At work I maintain a spreadsheet of all the company's clients and the progress that we make for each one. Essentially, I keep a huge sheet with all sorts of information that each week my boss wants specific versions of this sheet as a report. This means that I spend an immense amount of time each week cutting and pasting my big spreadsheet into lots of little spreadsheets (there are eight versions I currently have to produce) and then distribute them to each member of our staff based on their responsibility.

Now, I was wondering if there was any way to make Excel auto-populate these reports for me. In my mind, and I don't have any sort of formal Excel instruction, it seems like I could set the spreadsheet to automatically pop things into different tabs, i.e. if a client record is marked as "closed", that same entry would pop up under a tab marked "closed". Now, I'm not certain that Excel could even do such a thing, can it?

This isn't a do-or-die, but these weekly reports are getting quite unwieldy and errors inevitably pop up from time to time as I dissect and reassemble these spreadsheets on a weekly basis. If I can't make this process easier through Excel, I'm sure I can convince my boss to spring for tracking software, but a client-tracker in Excel that I have control over is far more appealing than the software that's out there.

Any help would be appreciated. My Google-Fu has come up with very little, mostly because I'm uncertain exactly what I'm even looking for.


Thanks!

Zoolander

This depends on the structure of your data, but I would keep one main spreadsheet that contains all the info, then create different sheets for each report. Then I would use formulas to just repeat whatever is in the main sheet exactly the same in the report. You can do this using a formula like "=MainSheet!A1", where MainSheet is the name of the main spreadsheet and A1 is the location of the data you need.

That way you keep all the info in the main sheet, and any changes you make to that data are reflected in the other sheets.

Raekreu

Zoolander wrote: »

This depends on the structure of your data, but I would keep one main spreadsheet that contains all the info, then create different sheets for each report. Then I would use formulas to just repeat whatever is in the main sheet exactly the same in the report. You can do this using a formula like "=MainSheet!A1", where MainSheet is the name of the main spreadsheet and A1 is the location of the data you need.

That way you keep all the info in the main sheet, and any changes you make to that data are reflected in the other sheets.

Zoolander has it right, this method is exactly what I would do in your situation.

Once you have the pages populating the data for a single client, all you would have to do is copy the data, open a new spreadsheet, and use 'paste special->values' so it won't attempt to reference the main spreadsheet. It's still kinda persnickety, but it will be a lot more accurate than copying by hand each time you need to submit a report.

Tef

As an addendum to what zoolander said, if you're really not that confident in writing functions, copy the data from the master spreadsheet and when you paste it into the new sheet:
right click the destination cell
Choose, 'paste special' and in the bottom right hand corner of the paste special dialogue box, there's a button saying 'paste as link' or something like that. Push that.

I've got a bit more advice but it'll have to wait for when I'm not trying to type on my phone, sorry.

Tef

Raekreu wrote: »

Zoolander wrote: »

This depends on the structure of your data, but I would keep one main spreadsheet that contains all the info, then create different sheets for each report. Then I would use formulas to just repeat whatever is in the main sheet exactly the same in the report. You can do this using a formula like "=MainSheet!A1", where MainSheet is the name of the main spreadsheet and A1 is the location of the data you need.

That way you keep all the info in the main sheet, and any changes you make to that data are reflected in the other sheets.

Zoolander has it right, this method is exactly what I would do in your situation.

Once you have the pages populating the data for a single client, all you would have to do is copy the data, open a new spreadsheet, and use 'paste special->values' so it won't attempt to reference the main spreadsheet. It's still kinda persnickety, but it will be a lot more accurate than copying by hand each time you need to submit a report.

My understanding of it is that if he can make his separate reports link back to the master sheet, it won't even be necessary for him to copy n paste any information again, at all

The Crowing One

Thanks for the pointers. From what I've been able to see I'd use functions or "vlookup" to get data from one sheet to another.

Not being an Excel person, I forgot to mention that the data that needs to be populated is both varied and multi-cell. Each client entry takes up a full row of 11 Cells. I need to be able to pull data based on common cell entries.

For example: I need to pull two types of reports, basically. The first is simply sorting and populating by who clients are assigned to, which is one of the cells. I want to be able to pull all of the "Crowing" cells out from the "Dave" and "Mary" cells and onto a second sheet. Secondly, I need to pull reports based on a date range, i.e. pulling all rows of client info who haven't been contacted in over a week.

I'm sorry, it's more complex than I first presented it as. I need to populate whole rows of info info multiple reports each based off the contexts of different, specific columns per report.

Tef

Vlookup is a handy tool. I'd recommend you dedicate part of your master sheet to a section containing all of your vlookup functions. You can then link that separate vlook table into your report sheets.

I've got plenty of training info relating to this that'd be really handy for you. I'll get on to it sometime tomorrow and PM you the info

LaOs

If I were you (and if I'm understanding your goals), I would keep the main worksheet and add one additional worksheet to the workbook (tabs) for each of the little mini-reports you will need to run.

I'd then use a formula to reference all the static information, like headers, etc. for all of the separate tabs. (=Main!A1 and so on). Then, depending on the "deciding value" for each of the tabs, I would write IF statements either copying information from the Main sheet or copying a blank cell (=IF(Main!$C2="Closed",Main!A2,"")).

This formula would be copied into each cell in the sub tab so it would pull information for each column from the Main sheet only if "Closed" is entered in the right column. Otherwise, it just results in a blank row. This may seem strange, since you'll just end up with each tab as large as the Main sheet except with a bunch of blank rows all over depending on the deciding values.

What I would do when I need to run a report for my boss is open the Main sheet file, save it, and then go to the tab of the report I want. Then I would highlight all of the data in the tab, including the blank rows (but not including the header row) and Sort so that all the blanks end up at the bottom. Then I'd just set my Print Area to only include the rows that actually have data instead of blanks and voila! I've now printed the report for "Closed" cases without having to copy or paste any data (risking errors) and I haven't "stopped" any of the actual work the sheet does, because I'll just close the file and not save my changes, ensuring the original file still has all the proper references going on.

Basically, a sub tab would look like this (formula-wise):

A1=Main!A1 || B1=Main!A2 || C1=Main!A3 || D1=Main!A4
A2=IF(Main!$C2="Closed",Main!A2,"") || B2=IF(Main!$C2="Closed",Main!B2,"") || C2=IF(Main!$C2="Closed",Main!C2,"") || D2=IF(Main!$C2="Closed",Main!D2,"")
A3=IF(Main!$C3="Closed",Main!A3,"") || B3=IF(Main!$C3="Closed",Main!B3,"") || C3=IF(Main!$C3="Closed",Main!C3,"") || D3=IF(Main!$C3="Closed",Main!D3,"")

And so on, as many columns across as you need and as many rows down as you need (or could possibly need--if you want a blank in the Main sheet to be appear blank in the sub tab until there's data to pull, those formulas end up looking like this: =IF(Main!A2="","",IF(Main!$C2="Closed",Main!A2,""))).

[Fake Edit]
Per your latest post: I would make a sub tab for each person (one for Crowing, one for Dave, one for Mary, etc.) and just work the same way as above.

For the date range, how is that driven? Are you just looking a a Latest Contact Date column on the main sheet and then doing the mental math to figure out that Client X hasn't been contacted in 8 days or more? If it's that, the formulas get a little trickier, but not much. (If you're actually editing the Main sheet with something like "overdue" or whatnot, that's just the same formula action as above, really.)

Checking to see if Today's Date is more than 7 days after the Last Contact Date and then deciding to either pull that information into the sub tab or generate a blank would give you a formula like this: =IF((TODAY()-Sheet1!$B2)>7,Sheet1!A2,"").

You can also check multiple cells in deciding to pull data or blanks, but I can go into that if you want.

Let me know if you have any questions or something doesn't seem right.

[Real Edit]
Basically, it ends up with some clean-up work you have to do before printing off reports for your boss, but it ensures that you never "lose" information in a cut+paste transfer and there are no transcription errors either. It seems to me that a quick sort and print area set-up is a lot quicker and cleaner than a bunch of copy+paste action.

The Crowing One

Woah, LaOs, that's fantastic!

I'll have to get back to this a little later, but there's a lot of info there. I really appreciate it. Hopefully I'll have a chance to try this out by the end of the day.

Thanks!

LaOs

I'll likely be around all day, so pop in with questions and whatnot as you have them. Hope it helps!

The Crowing One

It works! I plugged in the =IF(Main!$C2="Closed",Main!A2,"") formula and got exactly what I was looking for! Thank you!

Now, is there any way to speed up the process of putting in these formulas? I'll have to do this, it seems, by hand and copy/paste changing the values each time. Since I'm looking at a total of hundreds of entries, can this process be sped up at all?

LaOs

Write that formula once in the top-left corner of the sheet (well, left-most and 2nd row, below the headers). Then select that cell and as many cells to the right as there will be columns, and as many cells down as there will be rows (you may have to look at the main sheet to see how many rows you have*). Once you have the entire "area" selected, Ctrl+R and Ctrl+D will fill right and fill down, copying the formula to all of those cells you have selected (and the cell references will change appropriately).

*

To quickly see how many rows you have in the Main sheet, select the second row (first row of data after the headers) and hold Ctrl and Shift and tap the down arrow. This will highlight all the rows down to the very last one. A tooltip sort of box will appear under the last row, telling you how many rows you have.

[Edit]
For each sub tab, write the formula once in A2 (basically), change the "deciding value" in the formula, and then fill right and fill down as needed.

The Crowing One

Everything worked perfectly.

My last question (I assume) is going to be this: I have a bunch of values I need caught in the same formula as above, except these do not have identical cell contents. What I have is a bunch of coded files like "BR0008" and "BR0015" that I need to populate on a different tab. Is there a wildcard? Could I use "BR*" or similar to catch everything in that column that begins with "BR..."?

Thanks so much!

JihadJesus

Is this all the information in that spreadsheet is used for? It sounds like something that might also be handled well by a simple Access database and that might be more useful if you need it for other purposes as well. That would of course be a lot more involved to create and re-populate, though.

LaOs

The Crowing One wrote: »

Everything worked perfectly.

My last question (I assume) is going to be this: I have a bunch of values I need caught in the same formula as above, except these do not have identical cell contents. What I have is a bunch of coded files like "BR0008" and "BR0015" that I need to populate on a different tab. Is there a wildcard? Could I use "BR*" or similar to catch everything in that column that begins with "BR..."?

Thanks so much!

Since you're asking, I'm guessing it doesn't just need to capture every BR0015 but, perhaps, everything in the BR series? Like, what's the data look like in there?

If it's something like you need to pull all the data in the BR series, you could change the data-pulling formula like this:

=IF(LEFT(Main!$C2,2)="BR",Main!A2,"")

This looks at the first 2 characters from the left of the contents in cell C2 on the Main sheet to see if they are "BR". If they are, it pulls the data. If not, it generates a blank. You can use this formula to check for any type of series prefix, changing the number after in the second part of the LEFT formula depending on the number of characters you want to look for. Does that make sense?

If that's not exactly what the data is like, let me know, and we can figure out what will work.

[Edit]
Also, yeah, this is probably something that Access would be better for... if you knew how to work with Access and database tables and whatnot. I don't, so I'll leave that to someone else to offer help with. VThornheart is good with Access and a helpful sort of poster, if you like.

Tef

*huff* *puff* Ok. I got that info you needed!

You just need to...

Oh..

Looks like I'm not needed after all

LaOs

Aww. !

illig

Id highly suggest picking up a book on excel formulas and one on writing macros.

Excel can do some amazing things if you know a bit of the above... and management really considers this to be black magic... there are countless people spending hours or days every week manually massaging data out of excel spreadsheets. If you know how to automate the process, you are a walking money printer. Keep that in mind.

LaOs

The people on this forum are incredibly helpful and most are willing to nurture your growth along the Excel path if you're interested in learning more complicated formula and some macro skills. I've had them help me out at time or two with a macro (essentially came to them with a problem, how I wanted a solution to work, and they offered some code and explained a bit how it worked). It was awesome and I looked super impressive to my boss. :P