January 10, 2010

Tutorial for prepopulating or automating a range of cells

I began developing a quick invoicing system through Excel the other day and stumbled upon a solution to the problem of prepopulating cells. Well, I say stumbled, actually it was more like toiled for 5 hours. The idea came about because I wanted to be able to print off invoices and envelops without having to create new pages for each invoice. I wanted to be able to tell excel to prepopulate several cells on a template, if I gave it which row of information I wanted. This method involves a distinct setup I think many will find interesting and simple to implement.

Here goes...

(1) Create an Excel Workbook. I'm using Windows 7 and Excel 2007. (2) One sheet I call "data" and then I delete the other sheets just for clarity. You can always add more later. (3) In the first 3 columns, begin do a your data entry for the information you wish to store. You'll notice on mine, I have "date", "invoice" and "customer" as my headings. It would be good to point out here that although I have only 3 columns, in reality you could have as many as 127. (4) Now create the "key" column and put an "x" in any row. This "x" will make sense later. (5) Copy and paste the following formulas...

...for column 5...=IF(RC[-1]="X",RC[-4], "")
...for column 6...=IF(RC[-2]="X",RC[-4], "")
...for column 7...=IF(RC[-3]="X",RC[-4], "")

So, we've just told Excel to show the data from the 3 cells, if there is an "x" in the blue cell and if there is not an "x" don't display anything. You should, at this point, be able to put and "x" in any of those blue cells and the data to the right should display for you. But, we're not finished here because the cell location of the data we want to call up will still move based on where the "x" is located. (6) So, just below those cells were going to create 3 formulas that will copy any information displayed above them. To do that, copy and paste the following formulas...

...for columns 5 and 6...=SUM(R[-12]C:R[-2]C)

..."SUM" works well here because we opted to display nothing in the other cells, so it will only pick up on any numbers displayed above it. For cells that have text, though...

...like column 6...=TRIM(R[-12]C&R[-11]C&R[-10]C&R[-9]C&R[-8]C&R[-7]C&R[-6]C&R[-5]C&R[-4]C&R[-3]C&R[-2]C)

...I used "&" in this case because I think "CONCATENATE" has some limitations on its use and its just longer to type out. Depending on how many rows of information you have entered, you will see that this will be a very painstaking task to enter in all the "&"s and cells. What "TRIM" does in this formula, is it eliminates any extra delimiters that will screw up your formatting later on. You can now play around with the "x" location and see how it affects our prepopulated cells. (7) At this point, many of you should be familiar with how to "copy", right click, "paste special" and "paste link" into your template for publishing. If not, I can do a follow-up to this post. Let me know if you need one. (8) Once you're finished, you should see a spreadsheet that looks similar to this.

Here's is that same spreadsheet, but with some of the same instructions listed on it.

Here's a link to the spreadsheet used to create these graphics.

Couple of notes to go along with this tutorial. The 3 formulas at the bottom would actually be better located at the top somewhere, because most data tables grow down and it would be a huge hassle to redo the formulas each time. I put them at the bottom in this example just for clarity of information. Next, be mindful of the number of columns Excel limits each sheet to. For every column of information you want "automated" you will need one column for formulas to the right.

I would love your comments and questions. Best of luck. Here are some other excel ideas.

No comments :

Post a Comment