Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: More than 7 functions

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •