I have a complex spreadsheet that consists of 8 worksheets. Most of
these worksheets are dynamic lists which can be added to. They are
linked to a series of other spreadsheets that extracts informtion
concerning individual projects

The main information- a worksheet called expenses" is kept on one
sheet and currently has about 13000 entries. The spreadsheet database
itself is about 4.5 meg.

One worksheet (called Input) has been created which feeds into the
main sheet "expenses" transferring the information across to the 20+
columns.

No problems so far??!!

However my boss now wants a portable spreadsheet that can be sent out
to offsite employees which can then be imported into the main sheet.
The problem is that he could like it idiot proof which would entail
having dropdown lists like the input worksheet and a simple menu to
drive it - with protection all over the place so that they cannot
screw it up.

Unfortunately I cannot see a way of extracting the "input" form which
requests the relevant information without having to also include all
the other worksheets that have the up to date lists in them as he
wants dropdown lists with data validation. A snapshot of the lists at
the date of the email would however be sufficient

Ideally I would like to automatically create a partial spreadsheet
which could be emailed - from a button - once a month - to each of
these offsite staff with the same column headings as the expenses
sheet but with up to date dropdown menus in each column so that they
can only select information already stored in the main sheet. I do
not want them to be able to input their own project numbers etc and of
course as the spreadsheet grows in size - if we were to send the
original whole spreadsheet then we would encounter email size
problems.

I have tried this many years ago using Access but really do not want
to have to rewrite all the coding to create a new database.

Has anyone come across this problem before and come up with a
solution.

Any thoughts would be appreciated

Brian

Perhaps give them a blank template for each month. At the end of the month
they email the completed for you to APPEND to the main file.??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BJ&theBear" wrote in message
...
I have a complex spreadsheet that consists of 8 worksheets. Most of
these worksheets are dynamic lists which can be added to. They are
linked to a series of other spreadsheets that extracts informtion
concerning individual projects

The main information- a worksheet called expenses" is kept on one
sheet and currently has about 13000 entries. The spreadsheet database
itself is about 4.5 meg.

One worksheet (called Input) has been created which feeds into the
main sheet "expenses" transferring the information across to the 20+
columns.

No problems so far??!!

However my boss now wants a portable spreadsheet that can be sent out
to offsite employees which can then be imported into the main sheet.
The problem is that he could like it idiot proof which would entail
having dropdown lists like the input worksheet and a simple menu to
drive it - with protection all over the place so that they cannot
screw it up.

Unfortunately I cannot see a way of extracting the "input" form which
requests the relevant information without having to also include all
the other worksheets that have the up to date lists in them as he
wants dropdown lists with data validation. A snapshot of the lists at
the date of the email would however be sufficient

Ideally I would like to automatically create a partial spreadsheet
which could be emailed - from a button - once a month - to each of
these offsite staff with the same column headings as the expenses
sheet but with up to date dropdown menus in each column so that they
can only select information already stored in the main sheet. I do
not want them to be able to input their own project numbers etc and of
course as the spreadsheet grows in size - if we were to send the
original whole spreadsheet then we would encounter email size
problems.

I have tried this many years ago using Access but really do not want
to have to rewrite all the coding to create a new database.

Has anyone come across this problem before and come up with a
solution.

Any thoughts would be appreciated

Brian

On May 3, 1:40*pm, "Don Guillett" wrote:
Perhaps give them a blank template for each month. At the end of the month
they email the completed for you to APPEND to the main file.??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"BJ&theBear" wrote in message

...



I have a complex spreadsheet that consists of 8 worksheets. *Most of
these worksheets are dynamic lists which can be added to. *They are
linked to a series of other spreadsheets that extracts informtion
concerning individual projects

The main information- a worksheet called expenses" is kept on one
sheet and currently has about 13000 entries. *The spreadsheet database
itself is about 4.5 meg.

One worksheet (called Input) has been created which feeds into the
main sheet "expenses" transferring the information across to the 20+
columns.

No problems so far??!!

However my boss now wants a portable spreadsheet that can be sent out
to offsite employees which can then be imported into the main sheet.
The problem is that he could like it idiot proof which would entail
having dropdown lists like the input worksheet and a simple menu to
drive it - with protection all over the place so that they cannot
screw it up.

Unfortunately I cannot see a way of extracting the "input" form which
requests the relevant information without having to also include all
the other worksheets that have the up to date lists in them as he
wants dropdown lists with data validation. *A snapshot of the lists at
the date of the email would however be sufficient

Ideally I would like to automatically create a partial spreadsheet
which could be emailed - from a button - once a month - to each of
these offsite staff with the same column headings as the expenses
sheet but with up to date dropdown menus in each column so that they
can only select information already stored in the main sheet. *I do
not want them to be able to input their own project numbers etc and of
course as the spreadsheet grows in size - if we were to send the
original whole spreadsheet then we would encounter email size
problems.

I have tried this many years ago using Access but really do not want
to have to rewrite all the coding to create a new database.

Has anyone come across this problem before and come up with a
solution.

Any thoughts would be appreciated

Brian- Hide quoted text -

- Show quoted text -

Thanks Don for your advice

If however I give them a blank template then it is open for them to
input information which is not in the main database. I was trying to
restrict them to the dropdown list generated from the other worksheets
but without having to send them the other worksheets.

Thanks

Brian

Put the list in what you send and protect cells you don't want them to use.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BJ&theBear" wrote in message
...
On May 3, 1:40�pm, "Don Guillett" wrote:
Perhaps give them a blank template for each month. At the end of the month
they email the completed for you to APPEND to the main file.??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"BJ&theBear" wrote in
message

...



I have a complex spreadsheet that consists of 8 worksheets. �Most of
these worksheets are dynamic lists which can be added to. �They are
linked to a series of other spreadsheets that extracts informtion
concerning individual projects

The main information- a worksheet called expenses" is kept on one
sheet and currently has about 13000 entries. �The spreadsheet database
itself is about 4.5 meg.

One worksheet (called Input) has been created which feeds into the
main sheet "expenses" transferring the information across to the 20+
columns.

No problems so far??!!

However my boss now wants a portable spreadsheet that can be sent out
to offsite employees which can then be imported into the main sheet.
The problem is that he could like it idiot proof which would entail
having dropdown lists like the input worksheet and a simple menu to
drive it - with protection all over the place so that they cannot
screw it up.

Unfortunately I cannot see a way of extracting the "input" form which
requests the relevant information without having to also include all
the other worksheets that have the up to date lists in them as he
wants dropdown lists with data validation. �A snapshot of the lists at
the date of the email would however be sufficient

Ideally I would like to automatically create a partial spreadsheet
which could be emailed - from a button - once a month - to each of
these offsite staff with the same column headings as the expenses
sheet but with up to date dropdown menus in each column so that they
can only select information already stored in the main sheet. �I do
not want them to be able to input their own project numbers etc and of
course as the spreadsheet grows in size - if we were to send the
original whole spreadsheet then we would encounter email size
problems.

I have tried this many years ago using Access but really do not want
to have to rewrite all the coding to create a new database.

Has anyone come across this problem before and come up with a
solution.

Any thoughts would be appreciated

Brian- Hide quoted text -

- Show quoted text -

Thanks Don for your advice

If however I give them a blank template then it is open for them to
input information which is not in the main database. I was trying to
restrict them to the dropdown list generated from the other worksheets
but without having to send them the other worksheets.

Thanks

Brian