L
LPS
Using Excel 2000: I have a client who needs to take the data from one sheet
and automatically have it inserted into a second workbook, using a different
arrangement / placement of the data. The following attempts to describe the
setup:
The client has a workbook, with 1 sheet. This sheet is setup as a form in
which data is entered in the cell to the right of each field name. One of
the fields in this form identifies the location the data is coming from. It
is a "template" form so each time the client enters data into the form, she
saves the completed form under a new name.
There is a 2nd workbook with mulitple sheets identically setup, but each
sheet represents a different location. The column headers on each of these
sheets include the field names from the above "form template".
Every time the "form" is completed and saved, the client would like to have
the data from the form automatically copied to the correct location sheet, in
the 2nd workbook. For example, if the form is filled out for location 11,
she needs the data copied to Sheet 11 in the 2nd workbook, into the correct
columns. The desired end result is to have a sheet for each location
containing a list of all data from all forms created for that location
(confused yet?).
I have tried to figure this out using IF statements and Vlookups but they
don't work since the saved form's file name changes everytime the form is
completed. Now I have myself thoroughly confused.
Does anyone know a way of doing the above? If it cannot be done using
Excel, can the data be entered in a Word form and then copied to an Excel
worksheet (automatically)?
All help will be HUGELY appreciated. Sorry to be so "wordy".
Cheers,
LPS
and automatically have it inserted into a second workbook, using a different
arrangement / placement of the data. The following attempts to describe the
setup:
The client has a workbook, with 1 sheet. This sheet is setup as a form in
which data is entered in the cell to the right of each field name. One of
the fields in this form identifies the location the data is coming from. It
is a "template" form so each time the client enters data into the form, she
saves the completed form under a new name.
There is a 2nd workbook with mulitple sheets identically setup, but each
sheet represents a different location. The column headers on each of these
sheets include the field names from the above "form template".
Every time the "form" is completed and saved, the client would like to have
the data from the form automatically copied to the correct location sheet, in
the 2nd workbook. For example, if the form is filled out for location 11,
she needs the data copied to Sheet 11 in the 2nd workbook, into the correct
columns. The desired end result is to have a sheet for each location
containing a list of all data from all forms created for that location
(confused yet?).
I have tried to figure this out using IF statements and Vlookups but they
don't work since the saved form's file name changes everytime the form is
completed. Now I have myself thoroughly confused.
Does anyone know a way of doing the above? If it cannot be done using
Excel, can the data be entered in a Word form and then copied to an Excel
worksheet (automatically)?
All help will be HUGELY appreciated. Sorry to be so "wordy".
Cheers,
LPS