I have several cloumns of data 2000 rows deep.

column B contains a site number each site can then have several sets of data
column D contains a unique code for each line of data per site (ie col D has
the same codes in it but no site has the same code twice)
example
col b: col D:
100 A1
100 B1
100 B2
100 C1
101 B1
101 B2
102 A1
102 B1
102 C1

columns F to J contain monetary values

On a separate worksheet I have a form. I want to fill in a site code (say
in A1) and have the form be populated with the correct data for that site.
So in the form I fill in the site box 101 - in the form I have put all the
possible values that are in column D into a column into A5:A25. I want the
next columns to show the values from columns F - J so that I can add up the
sum of J for that site and calculate other info from it.

What do I need to put into the cells B5:F30 so that it reads the correct
data - just for that site. I am assuming it is a vlookup - but there is more
to it than that because there are 2 variables - also guessing its an array?
Margaret

Hi Margaret,

you can make it a bit easier for yourself if you insert a new column E
in the table and put in this formula:

=B1&D1

and copy this down. This makes a new unique reference for each row in
your table.

Then in B5 of your summary sheet you can enter this formula:

=VLOOKUP($A$1&$A5,sheet1!$E$1:$K$2000,COLUMN(C$1), 0)

This will bring the matching data from the old column F (now column G)
in your table, which I have assumed to be in Sheet1. The formula can
be copied across to column F on your summary sheet, and then you can
copy B5:F5 down to row 30. (I like it when posters give exact cell
details !!)

Hope this helps.

Pete

On Mar 28, 11:16*pm, MJJ wrote:
I have several cloumns of data 2000 rows deep.

column B contains a site number each site can then have several sets of data
column D contains a unique code for each line of data per site (ie col D has
the same codes in it but no site has the same code twice)
example
col b: * *col D: * * *
100 * * * *A1 * * *
100 * * * *B1
100 * * * *B2
100 * * * *C1
101 * * * *B1
101 * * * *B2
102 * * * *A1
102 * * * *B1
102 * * * *C1

columns F to J contain monetary values

On a separate worksheet I have a form. *I want to fill in a site code (say
in A1) and have the form be populated with the correct data for that site. *
So in the form I fill in the site box 101 - in the form I have put all the
possible values that are in column D into a column into A5:A25. *I want the
next columns to show the values from columns F - J so that I can add up the
sum of J for that site and calculate other info from it.

What do I *need to put into the cells B5:F30 so that it reads the correct
data - just for that site. *I am assuming it is a vlookup - but there is more
to it than that because there are 2 variables - also guessing its an array?
Margaret

Pete

Its easy when you know how - brilliant - thanks ever so much. I've been
struggling with that one for a while now and it works just as I want it to.
I've never used the & before (don't know why), but rest assured this is a
lesson well learn't. Thank you once again.

"Pete_UK" wrote:

Hi Margaret,

you can make it a bit easier for yourself if you insert a new column E
in the table and put in this formula:

=B1&D1

and copy this down. This makes a new unique reference for each row in
your table.

Then in B5 of your summary sheet you can enter this formula:

=VLOOKUP($A$1&$A5,sheet1!$E$1:$K$2000,COLUMN(C$1), 0)

This will bring the matching data from the old column F (now column G)
in your table, which I have assumed to be in Sheet1. The formula can
be copied across to column F on your summary sheet, and then you can
copy B5:F5 down to row 30. (I like it when posters give exact cell
details !!)

Hope this helps.

Pete

On Mar 28, 11:16 pm, MJJ wrote:
I have several cloumns of data 2000 rows deep.

column B contains a site number each site can then have several sets of data
column D contains a unique code for each line of data per site (ie col D has
the same codes in it but no site has the same code twice)
example
col b: col D:
100 A1
100 B1
100 B2
100 C1
101 B1
101 B2
102 A1
102 B1
102 C1

columns F to J contain monetary values

On a separate worksheet I have a form. I want to fill in a site code (say
in A1) and have the form be populated with the correct data for that site.
So in the form I fill in the site box 101 - in the form I have put all the
possible values that are in column D into a column into A5:A25. I want the
next columns to show the values from columns F - J so that I can add up the
sum of J for that site and calculate other info from it.

What do I need to put into the cells B5:F30 so that it reads the correct
data - just for that site. I am assuming it is a vlookup - but there is more
to it than that because there are 2 variables - also guessing its an array?
Margaret

Thanks for feeding back, Margaret. I'm glad you got more out of it
than just the solution to the particular problem.

Pete

On Mar 29, 4:56*pm, MJJ wrote:
Pete

Its easy when you know how - brilliant - thanks ever so much. I've been
struggling with that one for a while now and it works just as I want it to.. *
I've never used the & before (don't know why), but rest assured this is a
lesson well learn't. Thank you once again.



"Pete_UK" wrote:
Hi Margaret,

you can make it a bit easier for yourself if you insert a new column E
in the table and put in this formula:

=B1&D1

and copy this down. This makes a new unique reference for each row in
your table.

Then in B5 of your summary sheet you can enter this formula:

=VLOOKUP($A$1&$A5,sheet1!$E$1:$K$2000,COLUMN(C$1), 0)

This will bring the matching data from the old column F (now column G)
in your table, which I have assumed to be in Sheet1. The formula can
be copied across to column F on your summary sheet, and then you can
copy B5:F5 down to row 30. (I like it when posters give exact cell
details !!)

Hope this helps.

Pete

On Mar 28, 11:16 pm, MJJ wrote:
I have several cloumns of data 2000 rows deep.

column B contains a site number each site can then have several sets of data
column D contains a unique code for each line of data per site (ie col D has
the same codes in it but no site has the same code twice)
example
col b: * *col D: * * *
100 * * * *A1 * * *
100 * * * *B1
100 * * * *B2
100 * * * *C1
101 * * * *B1
101 * * * *B2
102 * * * *A1
102 * * * *B1
102 * * * *C1

columns F to J contain monetary values

On a separate worksheet I have a form. *I want to fill in a site code (say
in A1) and have the form be populated with the correct data for that site. *
So in the form I fill in the site box 101 - in the form I have put all the
possible values that are in column D into a column into A5:A25. *I want the
next columns to show the values from columns F - J so that I can add up the
sum of J for that site and calculate other info from it.

What do I *need to put into the cells B5:F30 so that it reads the correct
data - just for that site. *I am assuming it is a vlookup - but there is more
to it than that because there are 2 variables - also guessing its an array?
Margaret- Hide quoted text -

- Show quoted text -