+ Reply to Thread
Results 1 to 5 of 5

If text mathces column 1 range, display value in column 2 cell

  1. #1
    Registered User
    Join Date
    05-03-2008
    Posts
    24

    If text mathces column 1 range, display value in column 2 cell

    Help! I keep getting N/A or VALUE errors!

    Columns and Cells Name

    1. Activity Type Drop Down Options: Dme, O&m, blank (A3:A11)
    2. Cost if activities are DME (B1)
    3. Cost if activity if O&M (C1)

    If all cells in 1 are DME, then 2 = $1 and 3=$0
    If all cells in 1 are O&M, then 3=$1 and 2=$0
    If cells in 1 are mixed O&M and DME, then 2=$.5 and 3=$.5
    If all cells in 1 are blank, then 2=$0 and 3=$0

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    B1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "DME"), 1, IF(AND(A3:A11 = "O&M"), 0, 0.5) ) )

    C1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "O&M"), 1, IF(AND(A3:A11 = "DME"), 0, 0.5) ) )

    Both of these are array formulas; they MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

  3. #3
    Registered User
    Join Date
    05-03-2008
    Posts
    24

    Getting an error

    Using the formula format provided and ensuring I press Ctrl-Shift-Enter, I am getting an error.

    My information was an example scenario - would the fact that my real data is accross spreadsheets cause the error?

    Here is what I have:

    =IF(AND('Worksheet1'!I39:I98="",0,IF(AND('Worksheet1'!I39:I98="DME"),1,IF(AND('Worksheet1'!I39:I98="O&M",0,0.5)))

    So comparing to my example:
    'Worksheet1'!I39:I98 = A1
    'Worksheet2'!I2 = B1
    'Worksheet3'! = C1

    Another concern: for the range I39:I98, every other line will never have a value because they are used for styling purposes. Does this ruin the function?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    would the fact that my real data is accross spreadsheets cause the error?
    No. Can you post a workbook?

    ... every other line will never have a value because they are used for styling purposes. Does this ruin the function?
    Yes, it does.

  5. #5
    Registered User
    Join Date
    05-03-2008
    Posts
    24
    Please find the worksheet attached. Areas of interest are highlighted in BRIGHT ORANGE.

    'GeneralInformation'!I39:I98 is the range with the option of DME or O&M

    Part 1: If range 'GeneralInformation'!I39:I98 is all "DME" and some blank, then I want 'DME'!H28 to contain the full value of cell 'GeneralInformation'!I24

    Part2: If the range 'GeneralInformation'!I39:I98 contains "DME" AND "O&M" and blank, then I want 'DME'!H28 to contain .5(half) the value of cell 'GeneralInformation'!I24'. The other .5(half) will then display in 'O&M'!H28.

    Likewise, If range 'GeneralInformation'!I39:I98 is all "O&M" and some blank, then I want 'O&M'!H28 to contain the full value of cell 'GeneralInformation'!I24. Then follows the logic of Part 2 above.
    Attached Files Attached Files

+ 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