B
BJ&theBear
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
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