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

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 -

Hi All!

Thanks so much for your help...the folks on this forum are just amazing when
it comes to helping...just super!!!!

I am trying each formula to see which one is the best fit.

Roger,

When I paste yours in, then copy it to other cells, it works for two cells,
then starts returning #N/As in later cells.....very odd. I have checked and
only the cell refs on the active sheet are changing....the fixed refs for the
other sheet seem fine.

Here are the formulas as pasted in and the results:

=IF(AND(ISBLANK(A14),ISBLANK(C14)),"",IF(ISBLANK(A 14),VLOOKUP(C14,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A 14,Dept!$D$3:$F$24,3,TRUE)))

Results: Both A14 and C14 are blank, returns a blank cell instead of an
error.

=IF(AND(ISBLANK(A15),ISBLANK(C15)),"",IF(ISBLANK(A 15),VLOOKUP(C15,Dept!$D$3:$F$24,3,FALSE),VLOOKUP(A 15,Dept!$D$3:$F$24,3,TRUE)))

Results: Both A15 and C15 are blank, returns a #N/A error

I've tried pasting special, just the formula, then pasting normally. It's
just odd.

I'll post results for the other formulas as well.
--
Greg


"Roger Govier" wrote:

Hi Greg

=IF(AND(ISBLANK(A13),ISBLANK(C13)),"",
IF(ISBLANK(A13),VLOOKUP(C13,Dept!$D$3:$F$24,3,FALS E),
VLOOKUP(A13,Dept!$D$3:$F$24,3,TRUE)))


--
Regards
Roger Govier

"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

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 -

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

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