Posts by mcgee1505

    Understood.


    I also tried:

    Code
    1. =HYPERLINK(CELL("address",INDEX(DRN!$B:$B,MATCH(O2,DRN!$B:$B,1),1)))

    It showed me the address but the hyperlink still didn't work.


    Is there a VBA code where when I enter a document reference on column 'O' it creates the hyperlink to the corresponding document referenced in the other worksheet?

    Hi, I'm looking to use the HYPERLINK and VLOOKUP Functions to hyperlink to a cell on another worksheet. I have a worksheet (DATA) with document numbers in column 'O', and a worksheet (DRN) with a list of document numbers in column 'B'. I want to hyperlink the document number in column 'O' of the DATA worksheet and it take me to the document number in the 'DRN' worksheet. I typed in the formula

    Code
    1. =HYPERLINK(VLOOKUP(O3,DRN!B:B,1))


    but it just says 'cannot find file'. Can you help me to get this to work please?

    Hi JessieJoy,


    I'm pretty new to this but I had a similar issue to you previously and managed to work out with the help of the forum a solution that might work for you.


    Set the criteria
    In my example we look for the words 'Electrical' in column E, 'DUE' in column AA, but Column AB doesn't have 'SENT' inserted. This means we need to send an email.



    Then we need to set up the email, subject, body and recipients.



    Hope this helps


    Regards
    Mcgee1505

    Hi All,


    I've moved on to a new project and need a little help. I have a schedule of dates to track document submissions and at present I have to go through it line by line to see what is next due, importantly what is due from me. I would like to see the table represented on a timeline to make it easier for me to report upstream.


    The attachment is a snapshot of how the data is currently maintained.


    Any guidance or examples of how to get this into a timeline would be gratefully received.


    Regards


    McGee1505

    Hi All,


    I'm strugling with my workbook where I need to calculate how much time is lossed on my project.


    My workbook has a 'planned start' time a 'planned finish' time, an 'actual start' time and an 'actual finish' time, all these four times are automatically extracted from a separate report submitted every morning after the shift.


    My problem is these shifts run overnight i.e. 23:55 - 05:00, which is where I encounter the problem. I know it would be easy if the cells were date/time formatted, but the reports come from many departments and it would take ages to re-format all the other workbooks. They simply put in the times in the cells which I need to extract.


    In addition to this, I need to calculate the time lost between the planned and actual times. I have attached a workbook with an example of what I'm faced with, the first 3 rows have been manually inserted. What I need is the highlighted cells to contain a formula to make the calculations work?


    I hope someone can help as this would save me hours of manually working out the total hours and the amount we are losing?


    Colin

    Files

    • TimeLost.xlsx

      (10.09 kB, downloaded 9 times, last: )

    Hi All,


    I have a working sheet complete with formulas but I wanted to adjust a formula to reference a different cell. Currently I have:


    Code
    1. =IF(D8="Closed","Closed","")

    , whenever I change the D8 to reference K8, the formula shows in the cell rather than the desired response?


    Any advise would be great.

    All,


    I have a macro borrowed from a previous thread some time ago to write to a txt file whenever a cell is changed, and this works great. However, a colleague inserted a row and the macro brought up an error, is there something I can add to the code to allow the inserting of rows?



    Thanks in advance


    mcgee1505

    Re: Code for Toggle Button to Hide Rows


    Great question....


    The simple answer is that the idiot that created the worksheet didn't follow basic database guidelines and decided to replicate the header row down the page to separate each section of our project. So if I use the filter it hides all the individual section headers so we don't know which sections are which.


    Hope that makes sense...


    mcgee1505

    Hi All, hope you can help.


    I have a worksheet which in some rows contains the word 'Closed' in column B. When an item is closed I don't want to delete it as I may need the information at a later date. What I need is a code to place in a ToggleButton which will hide/un-hide the rows that contain 'Closed'.


    Hope this makes sense.


    Regards


    mcgee1505

    Re: Insert a row based on active cell


    Wow this is a good forum, I got part of the answer from another post


    Code
    1. Sub AddRow()
    2. With ActiveCell
    3. .EntireRow.Copy
    4. .EntireRow.Insert
    5. End With
    6. Application.CutCopyMode = False
    7. End Sub


    This works fine to insert the row and copies everything down, just need to limit the copy to first 5 columns?


    MG

    Hi everyone,


    I'm new to this forum so I hope you can help me. I'm trying to add a macro to insert a row and copy down the details in the first 5 columns only. The code below only selects certain cells and not the whole row, also it copies down the inserted cells and I need only copy down the first five columns, so I'm a bit stuck.


    Code
    1. Sub Insert1()
    2. '
    3. Range(Selection, Selection.End(xlToRight)).Select
    4. Selection.Insert Shift:=xlDown
    5. Selection.FillDown
    6. End Sub


    Hope you can help a newbie


    MG