pulling data from 2 columns
 

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

pulling data from 2 columns
 

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

pulling data from 2 columns
 

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

pulling data from 2 columns
 

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 -