Vlookup & If

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

Hi Jac

Debra Dalgleish has a sample workbook using advanced Filter and a macro
which will do this for you.
http://www.contextures.com/AdvFilterRepFiltered.zip

Also take a look at Debra's page on Advanced Filter
http://www.contextures.com/xladvfilter01.html#ExtractWs

--
Regards
Roger Govier

"Jac" wrote in message
...
Hi, I have a workbook, Master sheet containing this info :cell A4
contractor
name, B4 site location, c4 job cost etc to G4. There are several
contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to
all
in a4-a12. I then have a worksheet for each contractor. What I need to
do
is enter a formula in the individual contractor worksheets that pulls
across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody
help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.



__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Thanks Roger. Unfortunately it's not working for me, guess it's back to the
drawing board. Thanks anyway.

"Roger Govier" wrote:

Hi Jac

Debra Dalgleish has a sample workbook using advanced Filter and a macro
which will do this for you.
http://www.contextures.com/AdvFilterRepFiltered.zip

Also take a look at Debra's page on Advanced Filter
http://www.contextures.com/xladvfilter01.html#ExtractWs

--
Regards
Roger Govier

"Jac" wrote in message
...
Hi, I have a workbook, Master sheet containing this info :cell A4
contractor
name, B4 site location, c4 job cost etc to G4. There are several
contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to
all
in a4-a12. I then have a worksheet for each contractor. What I need to
do
is enter a formula in the individual contractor worksheets that pulls
across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody
help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.



__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Jac,
I am also doing the same job like yours. So my formula may help you (just i
am giving my suggestion, which I am using and getting good results). My
formula is I will insert a column before the Contractor and concatenate the
a4 and b4 Cells and then in the another work sheet I will use the vlookup
function.

Hey Jac, This is what i understand from your query. Don't blame if i am
wrong/incorrect.

Regards,

"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

Hey Vital_ar,
Am still trying to nut it all out too....hence posting in the "New User"
group. Thanks so much for your suggestions, I really appreciate it.

Cheers,
Jac

"Vital_ar" wrote:

Jac,
I am also doing the same job like yours. So my formula may help you (just i
am giving my suggestion, which I am using and getting good results). My
formula is I will insert a column before the Contractor and concatenate the
a4 and b4 Cells and then in the another work sheet I will use the vlookup
function.

Hey Jac, This is what i understand from your query. Don't blame if i am
wrong/incorrect.

Regards,

"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

Guys, am flappin my wings, but i aint flyin....Please, can anybody help me?
Have been trying to get lift off for days. Have given up on the macro and am
trying for a simple little old vlookup formula that will bring my information
across to each of my worksheets, cell by pitiful cell. Have changed my
spreadsheet around many, many times now and what I want to do is: bring info
from MASTERSHEET A4:G4 when sheet CONTRACTOR1 matches A1 with MASTERSHEET K4.
Anxiously awaiting any replies before I slip into the black hole of excel.



"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

Hi Jac
I'm not sure what you want but try this :
=IF(Mastersheet!$K$4=Contractor1!$A$1,Mastersheet! A4,"")
Copy this formula in any cell, any worksheet and drag across 7 rows and that
should do it, if I understand your question.
HTH
John
"Jac" wrote in message
...
Guys, am flappin my wings, but i aint flyin....Please, can anybody help me?
Have been trying to get lift off for days. Have given up on the macro and am
trying for a simple little old vlookup formula that will bring my information
across to each of my worksheets, cell by pitiful cell. Have changed my
spreadsheet around many, many times now and what I want to do is: bring info
from MASTERSHEET A4:G4 when sheet CONTRACTOR1 matches A1 with MASTERSHEET K4.
Anxiously awaiting any replies before I slip into the black hole of excel.



"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

If you're still not getting any thrust or lift from the JATO assists that
have been offered so far, then if you will entrust me with a copy of your
workbook, I may be able to help. If you want to try that route, send me an
email with the workbook attached and repeat the post/provide link to this
discussion in the email and I'll see what I can do to get you off the ground.
Send to (remove spaces)
Help From @JLatham site. com


"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.

John! You are legend! That is precisely what I wanted to achieve - many
thank yous.

"John" wrote:

Hi Jac
I'm not sure what you want but try this :
=IF(Mastersheet!$K$4=Contractor1!$A$1,Mastersheet! A4,"")
Copy this formula in any cell, any worksheet and drag across 7 rows and that
should do it, if I understand your question.
HTH
John
"Jac" wrote in message
...
Guys, am flappin my wings, but i aint flyin....Please, can anybody help me?
Have been trying to get lift off for days. Have given up on the macro and am
trying for a simple little old vlookup formula that will bring my information
across to each of my worksheets, cell by pitiful cell. Have changed my
spreadsheet around many, many times now and what I want to do is: bring info
from MASTERSHEET A4:G4 when sheet CONTRACTOR1 matches A1 with MASTERSHEET K4.
Anxiously awaiting any replies before I slip into the black hole of excel.



"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.



.

Thanks for the offer J Latham, much appreciated, however JOHN gave me push I
needed, and I'm up and soaring.

"JLatham" wrote:

If you're still not getting any thrust or lift from the JATO assists that
have been offered so far, then if you will entrust me with a copy of your
workbook, I may be able to help. If you want to try that route, send me an
email with the workbook attached and repeat the post/provide link to this
discussion in the email and I'll see what I can do to get you off the ground.
Send to (remove spaces)
Help From @JLatham site. com


"Jac" wrote:

Hi, I have a workbook, Master sheet containing this info :cell A4 contractor
name, B4 site location, c4 job cost etc to G4. There are several contractors
for the one job, so it may be that a4-a12 contain a contractor name, and
cells c4-g4 are the only cells that have information entered relevant to all
in a4-a12. I then have a worksheet for each contractor. What I need to do
is enter a formula in the individual contractor worksheets that pulls across
the information for each job (up to say 500rows, all roughly the same as
example above). the formula I have entered is only bringing across line
a4-g4 for the contractor who's name appears at a4, and not bringing across
the info relating to him at say cell a25. Using excel '07. Can anybody help
me? Auto filter no good, as I need the individual contractor sheets for
other uses, and must have the info separated out.

Any help much appreciated.