Simplify formula
 

I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it. An
IF function populates the other spreadsheets with the text from the Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project List
is blank.

This seems to be working, but I hope there is a more efficient way to do it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it, so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj

Simplify formula
 

I'm not really following your description but....

ISBLANK(MATCH(A6,ProjectName,0))

What are you trying to do with that expression?

That will never evaluate to TRUE.

Biff

"tjtjjtjt" wrote in message
...
I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it.
An
IF function populates the other spreadsheets with the text from the
Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project
List
is blank.

This seems to be working, but I hope there is a more efficient way to do
it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it,
so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj

Simplify formula
 

Let's start simple
The named range ProjectList looks like this with text in one column and
numbers in the one to the right.
Apple 3
Banana 6
Cherry
Damson 9

Then this formula on any sheet =VLOOKUP(A6,ProjectList,2,0) should return
the number 3 if A6 on that sheet has the text Apple.

If A6 has the text Cherry you will see 0. To avoid the zero use
either =IF(VLOOKUP(A6,ProjectList,2,0)0,VLOOKUP(A6,Proje ctList,2,0),"")
or =IF(VLOOKUP(A6,ProjectList,2,0)=0," ", VLOOKUP(A6,ProjectList,2,0))

Suppose the text in A6 is Plum which does not appear in the table?Then you
get #N/A. To get rid of this use
=IF(ISNA(VLOOKUP(A6,ProjectList,2,0))," ",IF(VLOOKUP(A6,ProjectList,2,0)=0,"
",VLOOKUP(A6,ProjectList,2,0)))
You cannot use
=IF(OR(ISNA(VLOOKUP(A6,ProjectList,2,0)),VLOOKUP(A 6,ProjectList,2,0)=0) ...

best wishes



--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tjtjjtjt" wrote in message
...
I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it.
An
IF function populates the other spreadsheets with the text from the
Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project
List
is blank.

This seems to be working, but I hope there is a more efficient way to do
it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it,
so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj

Simplify formula
 

I'll give this a try. Those stupid zeroes started this whole thing and it
mushroomed out of control.

--
tj


"Bernard Liengme" wrote:

Let's start simple
The named range ProjectList looks like this with text in one column and
numbers in the one to the right.
Apple 3
Banana 6
Cherry
Damson 9

Then this formula on any sheet =VLOOKUP(A6,ProjectList,2,0) should return
the number 3 if A6 on that sheet has the text Apple.

If A6 has the text Cherry you will see 0. To avoid the zero use
either =IF(VLOOKUP(A6,ProjectList,2,0)0,VLOOKUP(A6,Proje ctList,2,0),"")
or =IF(VLOOKUP(A6,ProjectList,2,0)=0," ", VLOOKUP(A6,ProjectList,2,0))

Suppose the text in A6 is Plum which does not appear in the table?Then you
get #N/A. To get rid of this use
=IF(ISNA(VLOOKUP(A6,ProjectList,2,0))," ",IF(VLOOKUP(A6,ProjectList,2,0)=0,"
",VLOOKUP(A6,ProjectList,2,0)))
You cannot use
=IF(OR(ISNA(VLOOKUP(A6,ProjectList,2,0)),VLOOKUP(A 6,ProjectList,2,0)=0) ...

best wishes



--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tjtjjtjt" wrote in message
...
I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it.
An
IF function populates the other spreadsheets with the text from the
Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project
List
is blank.

This seems to be working, but I hope there is a more efficient way to do
it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it,
so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj

Simplify formula
 

Your suggestions work. Thanks.

If I am reading your response correctly, the #NA error must be caught before
testing any other conditions. Is that correct? I am wondering exactly why the
OR can't be used.

--
tj


"Bernard Liengme" wrote:

Let's start simple
The named range ProjectList looks like this with text in one column and
numbers in the one to the right.
Apple 3
Banana 6
Cherry
Damson 9

Then this formula on any sheet =VLOOKUP(A6,ProjectList,2,0) should return
the number 3 if A6 on that sheet has the text Apple.

If A6 has the text Cherry you will see 0. To avoid the zero use
either =IF(VLOOKUP(A6,ProjectList,2,0)0,VLOOKUP(A6,Proje ctList,2,0),"")
or =IF(VLOOKUP(A6,ProjectList,2,0)=0," ", VLOOKUP(A6,ProjectList,2,0))

Suppose the text in A6 is Plum which does not appear in the table?Then you
get #N/A. To get rid of this use
=IF(ISNA(VLOOKUP(A6,ProjectList,2,0))," ",IF(VLOOKUP(A6,ProjectList,2,0)=0,"
",VLOOKUP(A6,ProjectList,2,0)))
You cannot use
=IF(OR(ISNA(VLOOKUP(A6,ProjectList,2,0)),VLOOKUP(A 6,ProjectList,2,0)=0) ...

best wishes



--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tjtjjtjt" wrote in message
...
I have a column of text that will have a column of numbers associated with
them.
I need to have the number appear on different spreadsheets when the text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to it.
An
IF function populates the other spreadsheets with the text from the
Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the text
reference cell on Project List is blank or the number column on Project
List
is blank.

This seems to be working, but I hope there is a more efficient way to do
it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing it,
so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj

Simplify formula
 

You are correct, you must trap the N/A error
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tjtjjtjt" wrote in message
...
Your suggestions work. Thanks.

If I am reading your response correctly, the #NA error must be caught
before
testing any other conditions. Is that correct? I am wondering exactly why
the
OR can't be used.

--
tj


"Bernard Liengme" wrote:

Let's start simple
The named range ProjectList looks like this with text in one column and
numbers in the one to the right.
Apple 3
Banana 6
Cherry
Damson 9

Then this formula on any sheet =VLOOKUP(A6,ProjectList,2,0) should return
the number 3 if A6 on that sheet has the text Apple.

If A6 has the text Cherry you will see 0. To avoid the zero use
either =IF(VLOOKUP(A6,ProjectList,2,0)0,VLOOKUP(A6,Proje ctList,2,0),"")
or =IF(VLOOKUP(A6,ProjectList,2,0)=0," ", VLOOKUP(A6,ProjectList,2,0))

Suppose the text in A6 is Plum which does not appear in the table?Then
you
get #N/A. To get rid of this use
=IF(ISNA(VLOOKUP(A6,ProjectList,2,0)),"
",IF(VLOOKUP(A6,ProjectList,2,0)=0,"
",VLOOKUP(A6,ProjectList,2,0)))
You cannot use
=IF(OR(ISNA(VLOOKUP(A6,ProjectList,2,0)),VLOOKUP(A 6,ProjectList,2,0)=0)
...

best wishes



--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"tjtjjtjt" wrote in message
...
I have a column of text that will have a column of numbers associated
with
them.
I need to have the number appear on different spreadsheets when the
text
appears appears next on that spreadsheet.

Text will appear on Project List sheet and may have a number next to
it.
An
IF function populates the other spreadsheets with the text from the
Project
List sheet.

I need a formula that will:
Return "" if there is no matching text on the Project List sheet, the
text
reference cell on Project List is blank or the number column on Project
List
is blank.

This seems to be working, but I hope there is a more efficient way to
do
it:

=IF(ISERROR(IF(OR(ISNA(MATCH(A6,ProjectName,0)),IS BLANK(MATCH(A6,ProjectName,0)),IF(ISNA(VLOOKUP(A6, ProjectName2,2,0)),"",VLOOKUP(A6,ProjectName2,2,0) =""),ISNA(VLOOKUP(A6,ProjectName2,2,0))),"",VLOOKU P(A6,ProjectName2,2,0))),"",IF(OR(ISNA(MATCH(A6,Pr ojectName,0)),ISBLANK(MATCH(A6,ProjectName,0)),IF( ISNA(VLOOKUP(A6,ProjectName2,2,0)),"",VLOOKUP(A6,P rojectName2,2,0)=""),ISNA(VLOOKUP(A6,ProjectName2, 2,0))),"",VLOOKUP(A6,ProjectName2,2,0)))

In some cases I was #Value and I couldn't figure out what was causing
it,
so
I wrapped the whole thing in a IF function and slapped it with ISERROR.

Any thoughts about how to tighted this up using Excel?
--
tj