Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg

Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have a
position in C1 and A1 is blank, I get the department. When both are blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet), I
get the department.
--
Greg


"Greg in CO" wrote:

Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg

The "General Form" is

=IF(ISERROR(..old equation..),"value to return if error",..old equation...)

so, using your formula:

=IF(ISERROR(IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3: $F$24,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE) )),"value
to return if
error",IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24 ,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE)))

unless you are using Excel 2007, which can handle errors more efficiently.

HTH,
Bernie
MS Excel MVP


"Greg in CO" wrote in message
...
Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and
C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have
a
position in C1 and A1 is blank, I get the department. When both are
blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet),
I
get the department.
--
Greg


"Greg in CO" wrote:

Hi All!

I have read through the postings and have tried on my own...the results
have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken
Plucker),
you enter the data in Column A; once the person has been assigned, you
enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1,
look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell
A1,
and returning the associated department; if cell A1 on Sheet A is blank,
then
the formula would look at cell C1 and do the same lookup. If both A1 and
C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department
will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along
with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel
really
thought of my formula.

All help is greatly appreciated.
--
Greg

Bernie, your formula appears to work fine. Thanks!
--
Greg


"Bernie Deitrick" wrote:

The "General Form" is

=IF(ISERROR(..old equation..),"value to return if error",..old equation...)

so, using your formula:

=IF(ISERROR(IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3: $F$24,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE) )),"value
to return if
error",IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24 ,3,FALSE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE)))

unless you are using Excel 2007, which can handle errors more efficiently.

HTH,
Bernie
MS Excel MVP


"Greg in CO" wrote in message
...
Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and
C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have
a
position in C1 and A1 is blank, I get the department. When both are
blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet),
I
get the department.
--
Greg


"Greg in CO" wrote:

Hi All!

I have read through the postings and have tried on my own...the results
have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken
Plucker),
you enter the data in Column A; once the person has been assigned, you
enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1,
look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell
A1,
and returning the associated department; if cell A1 on Sheet A is blank,
then
the formula would look at cell C1 and do the same lookup. If both A1 and
C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department
will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along
with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel
really
thought of my formula.

All help is greatly appreciated.
--
Greg

You could do it like this:

=IF(AND(A13="",C13=""),"",IF(A13="",VLOOKUP(C13,De pt!$D$3:$F
$24,3,0),VLOOKUP(A13,Dept!$D$3*:$F$24,3)))

Interesting that you have FALSE as the 4th parameter for the first
lookup, but TRUE for the second lookup.

Hope this helps.

Pete

On Aug 19, 11:20*pm, Greg in CO
wrote:
Yipee! *After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and C1
are blank, I get an #N/A error. *I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3*:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have a
position in C1 and A1 is blank, I get the department. *When both are blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet), I
get the department.
--
Greg



"Greg in CO" wrote:
Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. *So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. *If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg- Hide quoted text -

- Show quoted text -

Pete, your formula works fine as well...thanks!!!!
--
Greg


"Pete_UK" wrote:

You could do it like this:

=IF(AND(A13="",C13=""),"",IF(A13="",VLOOKUP(C13,De pt!$D$3:$F
$24,3,0),VLOOKUP(A13,Dept!$D$3Â*:$F$24,3)))

Interesting that you have FALSE as the 4th parameter for the first
lookup, but TRUE for the second lookup.

Hope this helps.

Pete

On Aug 19, 11:20 pm, Greg in CO
wrote:
Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3Â*:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have a
position in C1 and A1 is blank, I get the department. When both are blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet), I
get the department.
--
Greg



"Greg in CO" wrote:
Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg- Hide quoted text -

- Show quoted text -

You're welcome, Greg - thanks for feeding back.

Pete

On Aug 20, 4:41*pm, Greg in CO
wrote:
Pete, your formula works fine as well...thanks!!!!
--
Greg

As an FYI, the testing was:

Both A13 and C13 blank
Both A13 and C13 filled (all formulas returned the Dept value for A13..this
would be an invalid user entry, as it indicated that you had a planned and an
assigned resource using the same hours)
A13 filled
C13 filled

Thank again to all the gurus her who take the time to help...I have learned
so much from you folks!!!!

:o)
--
Greg


"Greg in CO" wrote:

Yipee! After much fiddling, I came up with a formula that does what I
need...however (there is always a however, isn't there?) when both A1 and C1
are blank, I get an #N/A error. I have tried to put the formula within an
ISERROR statement, but then I get the formula error message from Excel.

Ideas?

Here is the formula that seems to be working:

=IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FAL SE),VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE))

If I have a postion in A1 and C1 is blank, I get the department: if I have a
position in C1 and A1 is blank, I get the department. When both are blank, I
get a #N/A error; if both are filled (a no-no as far as using the sheet), I
get the department.
--
Greg


"Greg in CO" wrote:

Hi All!

I have read through the postings and have tried on my own...the results have
not been all that I would hope for....

Sheet A

Column A - a list of positions for planned resources
Column C - a list of positions for assigned resources
Column E - Department

Sheet B

Column D - a list of positions
Column F - the associated department for each position
_____________

Columns A and C have the same data in drop downs, but should not display
them at the same time. So if you have a planned resource (Chicken Plucker),
you enter the data in Column A; once the person has been assigned, you enter
their position in Column C.

I would like a formula on Sheet A in cell E1 so Excel would, on row 1, look
at cell A1 and do a lookup on Sheet B, finding the entry on Sheet A, cell A1,
and returning the associated department; if cell A1 on Sheet A is blank, then
the formula would look at cell C1 and do the same lookup. If both A1 and C1
are blank, E1 should be blank (versus an N/A or Value error)

This allows for a planned resource to be from one department, but if the
assigned resource is from a different department, the correct department will
still be in cell E1.

I have tried the SUMPRODUCT formulas I have, but they do counts versus
returning values. I tried 2 VLOOKUPs nested in an IF statement (along with
ISERROR arguments to eliminate the N/A or Value).....

Excel responded to both with the "You formula has an error" and when i
clicked ok, it highlighted the whole formula....telling me what Excel really
thought of my formula.

All help is greatly appreciated.
--
Greg