Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Multiply If statements and Deleting digits

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a fantastic forum and thanks in advance for the answer to my questions.

    1. I want to get a result a summary w/sheet to this scenario.

    If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
    How do I get this to work.

    2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

    Once again thanks
    Mike

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 22:05, Mikest wrote:
    This is a fantastic forum and thanks in advance for the answer to my questions.

    1. I want to get a result a summary w/sheet to this scenario.

    If E7=3,Project!A19:Q236,13 or IF e7=5,Project!A19:Q236,15 or E7=7,Project!A19:Q236,15
    How do I get this to work.

    2. I wish to delete the first or second digit plus the minus from this data (10-14 or 1-14) to give the result of 14

    Once again thanks
    Mike
    should that first 13 be 15 ?

    if so, try :

    =IF(OR(E7={3;5;7}),sum(Project!A19:Q236),15)

    (having a go at your second question)

    Hope this helps
    Chris


    2nd question :

    assuming A1 holds your data, try this :

    =REPLACE(A1,1,FIND("-",A1,1),"")+0

    (any find and replace experts, please don't laugh !!)



    [ This Message was edited by: Chris Davison on 2002-03-06 22:54 ]

    [ This Message was edited by: Chris Davison on 2002-03-07 11:17 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Chris,

    Question 2 answered perfectly.

    Question 1.
    Made a mistake on the last section should have read. 17 not 15.
    Summary: if E7=3 then display data in col13
    if E7=5 then display data in col15 and if E7=7 the display data in col17.

    Thanks Again.
    Mike

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sounds like you want...

    =INDEX(Project!A19:Q235,,E7+10)

    ...but, you said "summary" so the question is, "What kind of summary?" Perhaps, you want the sum...

    =SUM(INDEX(Project!A19:Q235,,E7+10))

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a set of room numbers and measurements on a worksheet page called projects.

    The idea is if a room has a cupboard 3 doors in it have a cell that tells me the size of the third door. (1 pair and 1 odd door)
    Or if 5 doors the size of the fifth and so on.

    All the third doors are in the 13th col, fifth doors are in 15 col and 7th doors are in the 17th col of the project w/sheet.

    Thank again
    Mike

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, if you want to list the 217 values associated with a cupboard type then select a vertical range of 217 cells and enter the array formula...

    {=INDEX(Project!A19:Q235,,E7+10)}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


    [ This Message was edited by: Mark W. on 2002-03-07 15:44 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a quick sample from my Projects w/sheet
    rm dr1 dr2 dr3
    1 2 3 4 5 6 7 8 13
    801B2,1,1,1400,3,1,Left,936,463,463,437

    Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
    the data I need. Cell E7 gives me how many doors.

    Have a try at that,
    Thanks
    Mike

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-07 16:01, Mikest wrote:
    Here's a quick sample from my Projects w/sheet
    rm dr1 dr2 dr3
    1 2 3 4 5 6 7 8 13
    801B2,1,1,1400,3,1,Left,936,463,463,437

    Then a have w/sheet called doors, where I enter the room No (801B2) and it displays all
    the data I need. Cell E7 gives me how many doors.

    Have a try at that,
    Thanks
    Mike
    Well, let's see...

    =INDEX(Project!A19:Q235,MATCH("801B2",A19:A235,0),E7+10)

    ...should give you the measurements for the doors in room 801B2.

    [ This Message was edited by: Mark W. on 2002-03-07 16:10 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again all,

    I have been working on the Foluma's suggested and get either a N/A or VAULE result.

    Any Thoughts
    Regards

    Mike

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
  •