Posts by pradeep_atm

  • Vlookup

    Re: Vlookup


    Hi Geetha,


    Try this:


    [vba]
    =VLOOKUP(D4,INDIRECT("'"&+(ROW()-3)&"'!$A$1:B2"),2,FALSE)
    [/vba]


    Change the references accordingly. I assumed that you are entering the data in 4 th row onwords and if it not so change the formula accordingly so that it gives 1,2 and so on..


    HTH.

  • Conditional Formula: greater than less than

    Re: Conditional Formula: greater than less than


    Hi dk,


    This should work. The formula either returns either TRUE or FLASE and accordingly the formating applied if the conditions returns TRUE.


    Please make sure you are making the proper settings.
    (CD-Formula Is...Format)


    HTH

  • Copy selected data to new worksheets via macros

    Re: Copy selected data to new worksheets via macros


    Hi,


    Welcome to the forum.


    Autofilter is the better option in selecting the data which meets a specific criteria. You can try applying the same to other data sets as well and post back with details, if you have any difficulty in doing so..


    HTH.

  • Conditional Formula: greater than less than

    Re: Conditional Formula: greater than less than


    Hi DK,


    You can use AND function. Try the following:


    AND($D$6>$D$7,$D$6>$D$8)
    AND($D$6>$D$7,$D$6<$D$8)
    AND($D$6>$D$7,$D$6<$D$8)
    AND($D$6<$D$7,$D$6<$D$8)
    AND($D$6<$D$7,$D$6=$D$8)
    AND($D$6=$D$7,$D$6>$D$8)
    AND($D$6<$D$7,$D$6=$D$8)
    AND($D$6=$D$7,$D$6>$D$8)


    HTH.

  • Changing format of date

    Re: Changing format of date


    Hi J,


    Once a cell is formatted as "dd-mmm-yy" It should display as 09-Mar-05 only.


    Of course, in the formula bar it will display as 03/09/05, which I don't think one can change, as this is the System default.


    HTH.

  • Sorting by color

    Re: Sorting by color


    Srinivasan,


    Example:


    Cell ref:
    A1: value(containing the word "System" etc.,)
    B1: Inserted Column if already blank cells are not there. In B1 type the same formula which you used in the Conditional formating.(Ex: =NOT(ISERROR(FIND("System",A1))) etc.,)
    C1: = CCI(A1,B1)


    C1 will give you the color index. However you can sort the data based on Column B which has either TRUE or FALSE.


    HTH.

  • If function - Shortcut?

    Re: If function - Shortcut?


    Hi David,


    You name the "Very Very Long formula" and use that name in the formula.


    For naming your formula: Insert - Name -Define - Refers to:


    Type your formula in Refers to: box and give a name to it in the Names in Workbook box(make sure to give a very very small name :wink: ).


    HTH.

  • Sorting by color

    Re: Sorting by color


    Hi Srinivasan,


    As explained in my earlier posts, you need to supply another parameter True or False(which is nothing but the outcome of your formula in the CD.


    =CCI(Cell Reference 1, Cell Reference 2)


    The first cell reference is your cell with CD. and the second one is which has the formula of the CD in it which returns either TRUE/FLASE.


    Instead of using the UDF you can insert another column with the formula in your CD entered into such that it will return either TRUE or FALSE and you can sort on this column.


    HTH.

  • Sorting by color

    Re: Sorting by color


    Well Norie,


    Sorry If in my earlier post if I have given an impression that it is not possible.


    It was a simple formula I agree. Prior to this, it was an UDF used to sort the data based on color index, and I went in those lines to solve this problem as well.


    BTW is there a way to test if a range is applied some CD.


    And given all these things let Srinivasan use the method which he feels comfortable..;


    Thanx

  • Sorting by color

    Re: Sorting by color


    Hi Norie,


    You are correct.


    The second column which I asked Srinvasan to insert to test the conditional formating condition will be alone enough to sort the data. However this is applicable only if there are only one condition attached to the CD.


    As I already said the solution which I suggested may not be an elegant one and there may be better way of handling this.


    Srinivasan,


    You can use the newly inserted column which tests the condition of the CD to sort the data, if you have only one condition attached to your CD.


    If you are using the code, You need not delete the earlier UDF, just add this new UDF to your module and start using the new UDF.


    HTH.


    thanx

  • Sorting by color

    Re: Sorting by color


    Hi Srinivasan,


    To sort the data having cells formating, you need to supply one more argument i.e TRUE or FALSE. Have one more column with the formula which you have mentioned in the conditional formating. This cell will return either true or False. So you need to select two cells in the CCI function(one is the usual cell which is the range and another cell is the new columns which you have inserted which tests the condition of the conditional formating.


    Though this is not an elegant way, this works if you are working on only the cells with the CD.


    [vba]
    Function CCI(rng As Range, test As Boolean)
    If test = True Then
    CCI = rng.FormatConditions(1).Interior.ColorIndex
    Else
    CCI = rng.Interior.ColorIndex
    End If
    End Function
    [/vba]


    HTH.

  • Advanced Filter

    Re: Advanced Filter


    Hi Kris,


    If you are trying to get the unique invoices then you try this:


    1. Move the data to new book(as this is going to be workings)
    2. Use Subtotals to count the data based on invoice number wise
    3. This will display invoice wise count of ...whatever
    4. Select visible cells(Ctrl+F5 &gt special - select visible cells)
    5. Copy and paste to a new sheet..


    Though a rude of doing, see if this helps. My settings here will not allow me to view your excel attachement(hope I understood your problem correctly).


    HTH.