+ Reply to Thread
Results 1 to 8 of 8

Tidier IF Statement or other function?

  1. #1
    Registered User
    Join Date
    08-30-2007
    Posts
    35

    Question Tidier IF Statement or other function?

    Hi there,

    I have quite a complex or conditional statement that ends up being very long when entered in an IF statement format. I was wondering whether this could be tidied up or done in another method! I have simplified the statement below to help you understand it easier!

    Please Login or Register  to view this content.
    Basically I have a table of data that normally i would VLOOKUP from (section in bold). The problem i have is that there are 5 exceptions where if a certain value is entered i need it to reference seperate tables (the first of these is in italic. I have a further four statements to add in like this. The only way i can see to do it is keep on creating more and more IF statements, but its getting very confusing!!! Is there a simpler way to do this???

    Mnay Thanks in advance
    Last edited by Richard Buttrey; 07-30-2008 at 07:28 AM. Reason: Set code tags around formula

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi Thomas,

    please enclose your formulas between code tags. Have a look at the forum rules for more info.
    Cheers

  3. #3
    Registered User
    Join Date
    08-30-2007
    Posts
    35

    Question

    I couldnt to keep the bold and italic in there you see....

    Can anyone help me with this problem?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by thomas.szwed
    I couldnt to keep the bold and italic in there you see....

    Can anyone help me with this problem?
    Hi,

    I've added the tags for you as Arthur suggested. You aren't restricted to the number of different type of tags. All you need do is select the text of the formula and click the # icon on the message menu.

    Back to your problem. You can avoid lots of IF() functions if you use INDIRECT(). Create a three column table of allowable values for C7. Put the C7 values in the first column, the range you want to use in the 2nd col. and the offset in the third column. e.g.

    Please Login or Register  to view this content.
    Name this range 'Table'

    Now use the following formula

    Please Login or Register  to view this content.
    HTH

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Conditional 5 different lookup tables

    "there are 5 exceptions where if a certain value is entered i need it to reference seperate tables"

    So the logic is
    if 1 --> Table 1
    if 2 --> Table 2
    ...

    It could perhaps be solved with:
    =INDEX() Syntax 2 (reference)
    From Excels help file:
    "For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4."
    Or the OFFSET, or the INDIRECT function.

    HTH
    Ola

  6. #6
    Registered User
    Join Date
    08-30-2007
    Posts
    35
    Thanks this solution works, but how do i name a table 'table'?

  7. #7
    Registered User
    Join Date
    08-30-2007
    Posts
    35
    I think i have worked out how to do this as i just entered in a name in a box to the left of the forumla bar.

    But I am now having problems with getting this statement to work....

    I think it may be to do with the value C7 and the table that you have asked me to create being on a different worksheet called 'Data'???

    Thanks for any help....

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Just highlight the table range concerned, and enter the word 'table' in the name box on the formula bar. It shouldn't matter that the table is on a different sheet.

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1