Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?

The easiest way would be to use a key value in a cell, and use that value to extract the relevant
data from your table using VLOOKUP formulas. So, for example, put a name into cell A1, and use
VLOOKUP formulas like:

=VLOOKUP($A$1,Sheet1!A1:D12,2,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,3,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,4,FALSE)


HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?

Thanks Bernie,

Would that mean I would have to change it each formula for each cell as we
changed contracts for differne people? (ie: using row 12, then row 13 etc...)

Rgs,

Ben.

"Bernie Deitrick" wrote:

The easiest way would be to use a key value in a cell, and use that value to extract the relevant
data from your table using VLOOKUP formulas. So, for example, put a name into cell A1, and use
VLOOKUP formulas like:

=VLOOKUP($A$1,Sheet1!A1:D12,2,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,3,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,4,FALSE)


HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?

Ben,

No, you would only be chaning the value in one cell, the key cell for the VLOOKUP formula. Then,
the VLOOKUP would return information from row 12 (when you enter the value from column 1 of your
data table that appears in row 12). And when you change the value in the key cell to that of row 13,
the information would change automatically. For the formulas below, you would change the value in
cell A1.

HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Thanks Bernie,

Would that mean I would have to change it each formula for each cell as we
changed contracts for differne people? (ie: using row 12, then row 13 etc...)

Rgs,

Ben.

"Bernie Deitrick" wrote:

The easiest way would be to use a key value in a cell, and use that value to extract the relevant
data from your table using VLOOKUP formulas. So, for example, put a name into cell A1, and use
VLOOKUP formulas like:

=VLOOKUP($A$1,Sheet1!A1:D12,2,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,3,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,4,FALSE)


HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?

Hi,

This advice was great, but I forgot to mention that the original data being
used is from a DIFFFERENT FILE, not just a different worksheet.

Any ideas?

"Bernie Deitrick" wrote:

Ben,

No, you would only be chaning the value in one cell, the key cell for the VLOOKUP formula. Then,
the VLOOKUP would return information from row 12 (when you enter the value from column 1 of your
data table that appears in row 12). And when you change the value in the key cell to that of row 13,
the information would change automatically. For the formulas below, you would change the value in
cell A1.

HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Thanks Bernie,

Would that mean I would have to change it each formula for each cell as we
changed contracts for differne people? (ie: using row 12, then row 13 etc...)

Rgs,

Ben.

"Bernie Deitrick" wrote:

The easiest way would be to use a key value in a cell, and use that value to extract the relevant
data from your table using VLOOKUP formulas. So, for example, put a name into cell A1, and use
VLOOKUP formulas like:

=VLOOKUP($A$1,Sheet1!A1:D12,2,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,3,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,4,FALSE)


HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?

Ben,

Simply open up the file with the data, and when you make up your VLOOKUP formula in hte first
workbook, switch to the other workbook when selecting the lookup table range. Excel will create the
link to find the data.

HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

This advice was great, but I forgot to mention that the original data being
used is from a DIFFFERENT FILE, not just a different worksheet.

Any ideas?

"Bernie Deitrick" wrote:

Ben,

No, you would only be chaning the value in one cell, the key cell for the VLOOKUP formula. Then,
the VLOOKUP would return information from row 12 (when you enter the value from column 1 of your
data table that appears in row 12). And when you change the value in the key cell to that of row
13,
the information would change automatically. For the formulas below, you would change the value
in
cell A1.

HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Thanks Bernie,

Would that mean I would have to change it each formula for each cell as we
changed contracts for differne people? (ie: using row 12, then row 13 etc...)

Rgs,

Ben.

"Bernie Deitrick" wrote:

The easiest way would be to use a key value in a cell, and use that value to extract the
relevant
data from your table using VLOOKUP formulas. So, for example, put a name into cell A1, and use
VLOOKUP formulas like:

=VLOOKUP($A$1,Sheet1!A1:D12,2,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,3,FALSE)
=VLOOKUP($A$1,Sheet1!A1:D12,4,FALSE)


HTH,
Bernie
MS Excel MVP


"MCNAB" wrote in message
...
Hi,

I'm trying to make a template for a contract whereby cells are left empty,
and these cells are filled with personal data from a worksheet. Is it
possible that I could leave a series of cells empty in the template and for
these cells to be filled by the personal data from a row of another worksheet?

Example: A row from a worksheet would contain personal data such as Name,
Employment No, length of job etc... in seperate columns. So each time I
wanted a new contract, I could click on the person's row in the Excel
Worksheet and the data would copy over to the empty cells and create a new
contract. Is there some way?