Auto populate Spreadsheet

**shane.albrandt** · 01-15-2010, 05:13 PM

Hi,

A few days ago I got some help auto populating a template and creating/saving new excel files based off of data from a separate tab. Thank you very much for that help. I've ran into a snag...

Please look at the attached template (Coumns N and Coumns O). Notice there are several unnecessary rows "Yes/No" check boxes. When the template creates the separate Excel files...it always keeps the same amount of rows....so if there are 100 rows on the template, and it creates an Excel file with only 5 people...there are 95 rows of blanks/check boxes.

I want to set up the template so that when I import information from the "Data" tab...it only puts the corrent amount of rows necessary....and doesn't keep a sheet with too many rows.

Make sense? I have a deadline of COB today to figure this out...so any help would be greatly appreciated!! Thanks so much!

So in other words...I would like there to be only the amount of rows as there are people in the Performance

**rwgrietveld** · 01-15-2010, 05:41 PM

One of the things is that Name:Data is defined as
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),13). It therefore only takes columns A:M and leave the N:Q.

There are several options.
1) Also include the checkboxes in the Data Sheet
2) Check the number of records and delete the rest (empty records) from N:Q in sheet Performance Management.
3) Dynamically add N:Q in your FilterData procedure.

Pick one ...

Next is my question why you use the checkboxes in the first place. These aren't used anywhere. Just for the esthetics? Why not a validation that take either "Yes" or "No". Much simpler to implement.

Just a note: Lucky for me, but what you didn't check was IF there is a C:/Temp
Code stops there, otherwise my C:Temp would have been full

**shane.albrandt** · 01-15-2010, 05:44 PM

Since I'm creating approximately 540 of these spreadsheets, it would be easiest not to go back and delete data from each of the 540 spreadsheets if you know what I mean. Is there a way to automate this??

I don't know how to do these two options....can you help me with it?
1) Also include the checkboxes in the Data Sheet
3) Dynamically add N:Q in your FilterData procedure.

**shane.albrandt** · 01-15-2010, 05:47 PM

I don't think I can do option 1. Because the source of this data is initially a text file, then I import it into Excel and place it in the "Data" tab. The text file isn't capable of having a check box column...if that makes sense. So I think my only option is option 3?? But I don't really understand it.

**shane.albrandt** · 01-15-2010, 07:17 PM

Can anyone help me with this? I'm stuck.

Thanks so much in advance for your time!

**shane.albrandt** · 01-15-2010, 08:00 PM

okay...I've ditched the chec boxes and now I'm just doing a drop down (Yes or No). So with that being said....it's still giving me blank rows. I just want the amount of rows that there are people/records.

Can ANYONE help?

**broro183** · 01-15-2010, 09:20 PM

hi Shane,

I'll have another look at this thread later in the day, & may find that Ricardo has you sorted by then. However, I think that it will be easier for helpers to help you if repost & upload a new sample file which contains the newest layout ie "ditched the chec boxes and now I'm just doing a drop down ".

Rob

**rwgrietveld** · 01-16-2010, 06:35 PM

Shane,

It's weekend in the Netherlands as well so hold your breath a little longer. Please answer this. It would make our live so much easier.

Originally Posted by rwgrietveld

Next is my question why you use the checkboxes in the first place. These aren't used anywhere. Just for the esthetics? Why not a validation that take either "Yes" or "No". Much simpler to implement.