More than 7 functions

[rdeters]

Maybe if chris can help me out with this one again. Thanks again for the help yesterday, that formula worked excellent. I have the following formula now:
=IF(OR(A1={"V-Valve (AO)","X-Motor (VFD)"}),"AO",IF(OR(A1={"V-Valve (DO)","X-Motor (Start/Stop)"}),"DO",IF(OR(A1={"ZO-open","ZC-close","ZO/ZC-open/close"}),"DI",IF(A1=("ZT-(Transducer)"),"DO",""))))

I want to replace "A1" in the formula above with: =INDIRECT("'Instrument Tags'!"&CHAR(SUBSTITUTE(MOD(ROW(),7),0,7)+76)&ROUNDUP((ROW()/7),0)+2).
Replacing it is no problem. The problem is that excel will not accept it, probably because it has more than 7 functions imbedded or maybe too many characters. Are you able to help me out any? Hoping that this one will not exhaust you as much as the last problem! Ha Ha.

P.S. I was able to figure out you're logic out, with the last problem that is, thanks.

[lenze]

Try placing your 2nd formula in another location and assigning a Name to that Cell. Then in your 1st formula, replace A1 with the Name. It should work.

[Aladin Akyurek]

On 2002-05-09 12:12, rdeters wrote:
Maybe if chris can help me out with this one again. Thanks again for the help yesterday, that formula worked excellent. I have the following formula now:
=IF(OR(A1={"V-Valve (AO)","X-Motor (VFD)"}),"AO",IF(OR(A1={"V-Valve (DO)","X-Motor (Start/Stop)"}),"DO",IF(OR(A1={"ZO-open","ZC-close","ZO/ZC-open/close"}),"DI",IF(A1=("ZT-(Transducer)"),"DO",""))))

I want to replace "A1" in the formula above with: =INDIRECT("'Instrument Tags'!"&CHAR(SUBSTITUTE(MOD(ROW(),7),0,7)+76)&ROUNDUP((ROW()/7),0)+2).
Replacing it is no problem. The problem is that excel will not accept it, probably because it has more than 7 functions imbedded or maybe too many characters. Are you able to help me out any? Hoping that this one will not exhaust you as much as the last problem! Ha Ha.

P.S. I was able to figure out you're logic out, with the last problem that is, thanks.

May I suggest a different route concerning your first formula?

Create a 2-column list in a worksheet you could name Admin as follows:

{"V-Valve (AO)",
"AO";"V-Valve (DO)","DO";
"X-Motor (Start/Stop)","DO";
"X-Motor (VFD)","AO";
"ZC-close","DI";
"ZO-open","DI";
"ZT-(Transducer)"")","DO"}

in A2:B8. Select all of the cells of this 2-column list, go to the Name Box on the Formula Bar, type CATEGS (from Categories), and hit enter.

Now change

=IF(OR(A1={"V-Valve (AO)","X-Motor (VFD)"}),"AO",IF(OR(A1={"V-Valve (DO)","X-Motor (Start/Stop)"}),"DO",IF(OR(A1={"ZO-open","ZC-close","ZO/ZC-open/close"}),"DI",IF(A1=("ZT-(Transducer)"),"DO",""))))

to:

=IF(COUNTIF(CATEGS,A1),VLOOKUP(A1,CATEGS,2,0),"")

Try to substitute the second formula for A1.

Aladin