Posts by alz1

    Hi, hope someone can help.


    This works:

    =COUNTIFS(Table1[Role],3,Table1[Skill1],1)

    Returning a count of instances of "1" where the Role column value in Table1 is 3


    Can someone please help explain why this doesn't work if C4 contains Table1 and D4 contains Skill1

    =COUNTIFS(Table1[Role],3,(INDIRECT(C4&"["&D4&"]",1)


    My aim is to inject the table and column references from cell values in C4 and D4.


    The INDIRECT syntax works with count but I cant get it to work with the condition of the other column (role) value.


    Thanks.

    [ATTACH=CONFIG]64531[/ATTACH]



    This is cut down example of what i'm trying to achieve.


    table 1 has a value that i lookup in table 2 in a separate sheet. for that value found in table 2 there can be multiple rows with the same original value, i want to display a count of the ocurrences of a text string in an adjacent column in table 2 - but display the count in table 1. Example attached.


    I might be asking too much for one formula, any help appreciated.

    Files

    • Example01.xlsx

      (10.84 kB, downloaded 26 times, last: )

    I have a formatted table with a "Due Date" column.



    I've currently got 3 nested IF statements to calculate if "Red", "Green" or "Amber" is displayed as result of the formula. I then use this text value to apply conditional formatting.



    However, I'm having difficulty because I want one IF statement to override another.



    There are the requirements for what I would like to achieve - any help appreciated.



    Rule 1: IF [@[Due Date]] is today or less display text: "Red"



    Rule 2: IF [@[Due Date]] is greater than or equal to today display text: "Green"



    However...



    Rule 3: IF [@[Due Date]] is greater than today but within 3 days, override rule 2 and display "Amber"



    This is the working formula I have:



    =IF([@[Due Date]]-TODAY()<=0,"Red", IF([@[Due Date]]-TODAY()>=0,"Green", IF([@[Due Date]]?TODAY()??,"Amber")))

    I have categories across the columns and codes that apply to the categories in rows.


    Category1,Category2,Category3
    Code1, Code2, Code3
    Code2, Code9,Code5



    In this example code2 appears in Category1 and Categroy2


    I'm looking for help with an easy way to report on common codes across categories. There are over 100 categories in my real data.



    Ideally in a matrix with all codes along the top row and categories down the first column.

    I've looked at some examples two formulas in one cell but not found what I need.


    I have a list of identifiers in a formatted table in one sheet with a column that signifies if they have a two or three character prefix:


    (pipe delimited here for illustration only)


    AB001 | Two
    AB002 | Two
    CD001 | Two
    CD002 | Two
    EFG001 | Three
    GHI002 | Three



    In a separate sheet in the same workbook I want to show the character prefix only in its own column.


    The user types "AB001" into column A, Excel puts prefix only in column B.


    AB001 | AB
    AB002 | AB
    CD001 | CD
    CD002 | CD
    EFG001 | EFG
    GHI002 | GHI


    This can be done "manually" with these in column B:


    =LEFT($A2,2)
    =LEFT($A3,3)


    I want Excel to decide which formula to use ("=LEFT($A2,2)" or "=LEFT($A2,3)" by looking up the value "Two" or "Three" in the table in the other sheet.


    I'm using Excel 2010.


    Any help appreciated.

    Re: apply different conditional formating to border based on value in cell above/belo


    Soulfire, really helpful - thanks! A step in the right direction. I've manually mocked up what I want to achieve in the attacted file.


    Although I don't know how to write such a formula, could OFFSET be used pass the value of the cell above/below in to the formula (if cell above/below is odd/even - or if different from current cell) to then apply the formatting.


    What I want to achieve is possible with shading but for this activity I need to avoid using shading and use border formatting, if possible.

    I want to use conditional formatting to format rows if the value in the cell above is odd and use different formatting if the value in the cell below is even.
    I can shade rows using

    Code
    1. "=ISEVEN($A1)

    " etc based on odd even values. but i want to apply a different border formatting depending on the odd or even value in the cell above/below.


    To explain it as an example: I have 9 rows. Rows 1-3 have "1" in A1, A2, A3; 4-6 have "2" in A4, A5, A6; 7-9 have "3" in A7, A8, A9; I want to use conditional formatting to have an outline border round rows 1-3, 4-6, 7-9 - based on the odd or even value. (instead of shading the rows using isodd/iseven).


    Might not be possble but thought I'd ask if it was perhaps obvious to an expert at formulas - which I'm not! :) Any help appreciated.

    Re: Inject formula into cell based on lookup value


    Thanks NBVC! Very useful. I've used your example and a modified version of it:


    =IF(B2="1",LEFT(A2,5)&" "&MID(A2,6,3)&" "&RIGHT(A2,3),IF(B2="2",LEFT(A2,4)&" "&MID(A2,5,3)&" "&RIGHT(A2,4),"Not Specified"))


    The result is a much neater spreadsheet where I can copy down the formula easily. The help from this forum is always appreciated.

    I have a formula that puts spaces in a string:


    "=LEFT(A5,H5) &" " &MID(A5,I5,J5) &" " &RIGHT(A5,K5)"


    Value "00000000000" (in A5) becomes "00000 000 000" in another cell. You can see I'm using values in other cells in the row to populate the formula.


    I've adjusted the formula values to also display the string as "0000 000 0000"


    I want to determing the formula to use based on lookup - instead of copying different version of the formula down the column:


    =LEFT(A4,4) &" " &MID(A4,5,3) &" " &RIGHT(A4,4) equates to Style 1
    =LEFT(A5,5) &" " &MID(A5,6,6) &" " &RIGHT(A5,3) equates to Style 2



    Is it possibler to "inject" a formula into a cell based on a lookup value?


    Any help appreciated.