Posts by lorenzod

    Re: Font change depending on result


    hi....


    try this....


    Select Cells from Format menu
    Choose Number tab
    From Category... select Number or Currrency
    On Negative numbers... select the last option (negative in red color/parenthesis)
    Check 1000 separator (,), if u wish



    OR


    Select Cells from Format menu
    Choose Number tab
    From Category... select Custom
    On Type:, write on the space provided:


    _(* #,##0.00_);[RED]_(* (#,##0.00);_(* "-"??_);_(@_)



    Note: You can change [Red] with other color e.g. [Blue] or [Green]


    For detailed explaination of Custom Number Format, go here:


    http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

    Re: Activate hyperlink base on date


    Hi Andy,



    Thanks for the reply...
    And sorry for the delayed response....


    I tried ur formula... and it worked...
    But I wanted also to display the text, but not activate the hyperlink...


    I put it this way:


    =IF(D1>TODAY(),HYPERLINK("#Lesson1!A2","Lesson1"),"Lesson 1")


    Here's my problem now:


    If the date in D1, is less than TODAY(), there is still a hyperlink in the text Lesson 1, and once u click it... a message will display, "Cannot open the specified file."


    Is there something missing in my formula?


    Thanks again....

    Good day all!!!


    I have a file with data on certain cells that I wanted to hyperlink to another sheets. I wanted the hyperlink to be activated ONLY after a certain date - system date (say, if system date is greater than Jan 5, 2005, make a hyperlink on cell b1 in sheet1 to cell a2 in sheet2; if system date is greater than Jan12, make a hyperlink on cell b3 in sheet1 to cell a2 in sheet3, and so on). The link will remain active, of course, unless the system date is changed.


    Can someone help me on this pls...


    A million thanks...

    Thanks Barry...


    Just want to keep my workbook look "clean", that's y m hiding the sheet tabs...(m also hiding column and row headers, actually).


    Can you give me the codes for the events Workbook_SheetChange and Workbook_SheetActivate to accomplish this...?


    And may I know what "problems" you are facing with this?


    Thanks again...


    God bless..!!!

    I hide the sheet tabs of my file...
    and don't want anyone to display it by enabling the Sheet tabs in the Tools-Options-View menu


    I'm having this code ThisWorkbook but it's not working...
    some help pls....


    Thanks a mil.



    Sub HideTabs()
    If ActiveWindow.DisplayWorkbookTabs = True Then
    ActiveWindow.DisplayWorkbookTabs = False
    End If
    End Sub

    I'm also an amateur in VBA... but try this..


    use &vbcrlf& to force the text to go to another line....


    If you are typing your code in another line...don't forget also to put _ (underscore) before going to another line. The underscore means that the next line is a continuation of the previous line.


    hope this would help...

    try INDEX/MATCH function


    =INDEX($A$2:$B$4,MATCH($D5,$A$2:$A$4),2)


    You can even use Match again for the Col_num (2), if your column has a label.


    INDEX/MATCH is much better than VLOOKUP/HLOOKUP: in the former, the lookup value need not to be on the first column/row (as in VLOOKUP/HLOOKUP).


    Hope this helps...

    A B C D E F



    Department: Cost Center: 110100
    EXECUTIVE MANAGEMENT DEPT. ( GENERAL )
    Emplid OldEmpid Cost Center Name Nationality Profession
    00216 00115 INDIA NONE
    00499 00163 INDIA CLEANER
    Department: Cost Center: 110105
    INTERNAL AUDIT SECTION
    Emplid OldEmpid Cost Center Name Nationality Profession
    04449 02809 KENYA INT. AUDITOR



    The above data shows a file I have imported from our system to excel.


    Col A is for EmplID


    Col B is for OldEmpID


    Col C is for Cost Center (I inserted this col; this is where i want to copy the "cost center code" housed also in col F (e.g. 110100,110105)


    Col D is for Name


    Col E is for Nationality


    Col F is for Profession (This col contains also the "cost center code" that I want to copy to col C)



    My problem:


    If there is a value in Col A (EmplID) that starts with "0" (all EmpID starts with "0"), I will copy the corresponding "cost center code" in Col C.


    Condition:
    The word "Department" (housed also in Col A), triggers that a different set of "cost center code" will be copied to Col C.


    Actually, I also wanted the cost center in col C to be 5digits only, so I wanted to exclude the first digit.



    So in this case,
    Emplid 00216 and 00499 will have "cost center code" of 10100; and
    Emplid 04449 will have "cost center code" of 10105.



    I have lots of rows for this, so i need a VBA to automated/facilitate the copying of the Cost Center codes.


    Any help is highly appreciated.


    Thanks a mil.

    I got it folks... (got it somewhere on the threads, after several minutes of searching)...



    Private Sub UserForm_Activate()
    pgeMainMenu.Value = 0
    End Sub


    Thanks



    One question though: how can i search on threads with very specific topic (e.g. multipage userform default page). With my case, i just typed user form, and it gave lots of pages to look into....

    Hello!


    I am working on a multi-page userform. I want to make the first page (index 0) to be the default page when the form is activated (opened).


    I have tried these (but to no avail):


    Private Sub UserForm_Activate()
    pgeMainMenu.TabIndex = 0
    End Sub




    Private Sub UserForm_Activate()
    pgeMainMenu.Pages (0).SetFocus
    End Sub



    Any help is highly appreciated.

    You guys are supercalifragilisticexpialidocious!!!!


    I thing I have missed 2/3 of my professional carreer by not visiting this site before. I'm sure I have missed so many things out here. Is there a way to search for a specific topic (thread)? Is there a list of topics by category (let's say Filtering, Conditional Formatting, etc) where i can just simply click and view all the threads.


    My sincerest thanks for your help!!!


    More power to this site!!!!

    This is too fast...!!!!
    Thanks a lot Dreamboat... it works!!!!!


    But i have another file that needs to check two columns if empty (i mean, column A is empty, but Column C is not and vice versa). I want to delete the rows if both Column A and C are empty. How can I do that?


    Thank you in advance... I really do appreciate your help!

    If you also want to verify your merged document before printing, you can click the icon View Merged Data (the one with "abc" and << >> on top). You can then click Next Record to view the recipients one by one.
    You can also print a particular recipient if you want to. Just click Next Record until you reach the recipient and print.

    Thanks Derk! You're great...!!!!!


    (Sorry for the late reply....got so busy for so many reports....mid-year audit, huh!)


    Hope you can help me on this also...


    I have these data in b10:b20....


    A B C D E
    1
    2
    ...
    10 5 3%
    11 7 2%
    12 11 5%
    13 15 7%
    14 4 1%
    etc


    ...and wanted to multiply column A by column B in cells A2 to J2 (product of a10 by b10 will be in A2, a11 by b11 in B2, etc).


    Is there a single formula that changes the cell reference by rows (not by columns)?


    Thanks again...


    how can i do this in one formula, without changing the cell reference? I'm aware that when we copy formula to the succeeding columns, cell reference would change in columns also and not in rows. hope i made it clear