Posts by Averilp

    Hi all,


    What would be the formula I need to find the following Friday from a date? I figure that it would be a combination of WEEKDAY, DATE and CHOOSE but I can't get my head around it.


    I have read Dave's posts on finding the last Friday of a month etc. but I can't work out how to get the next occurring Friday from a particular date (not just the last). I.e. if cell D2 has 13/08/2008, I want E2 to have 15/08/2008 since that is the next occurring Friday.


    Thanks in advance.


    Averil[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]OK, I have the following now:


    =D2+(WEEKDAY(D2)>6)*6-WEEKDAY(D2)+6


    However, this doesn't work properly if a Saturday date is entered (it puts the next Thursday in). My brain is in overload this week so I apologise if this is an extremely simple question.


    Cheers,
    Averil

    Re: Remove Protection Of File & Unprotect Multiple Worksheets


    You need to use the sheets code name.


    You can see this in the VBE Editor in the Excel Objects. If you see your sheet name in the brackets, the code name is to the left. I.e. if you see Sheet3 (Panels_SQ) the sheets code name is Sheet3.


    Alternatively, use:


    Code
    sheets("Panels_SQ").Unprotect Password:="D8ts!js1"


    Cheers,
    Averil

    Re: Extract Data From Multiple Sheets Into One


    Dave,


    I have read through the above posts as well as others. In fact, I've been searching the site since 5am this morning, and no case seems to be like mine unless I am searching incorrectly, but I'll keep trawling through.


    In the mean time, does anyone have suggestions as to why the Select Case doesn't seem to be working as expected?


    Regards,
    Averil

    Hi there,


    This was originally going to be a simple Select Case question, but upon introducing my problem I realise that there is likely a much better way of attacking my "big picture".


    I know I will likely get told off for the code given that it is basically a series of loops and will be time consuming because of this. However, this is a one-time code that I need to run on this spreadsheet so I can get the data into a "correct" format for future use.


    Currently, I have hundreds of sheets that are simialr to the attached TA0632TEST and TA0632TEST2 sheets, each with a differing amount of columns based on the number of work days for that particular month. The code only needs to be performed on sheets starting with the initials of an employee (shown in select case in code).


    I've been thinking of better ways to have data entered/managed and had thought it best to have all raw data in a single sheet which can then be filtered/looked up/pivot tabled/custom viewed etc. and all the other fun stuff like how it should be done :-).


    I am open to other ideas about how best to get this historic data into a managable format.


    The problem I am running into at the moment is that where the Select Case is checking if the sheet name starts with the initials and project number, it doesn't seem to recognise if it is correct (and therefore perform the actions).


    I have stepped through the code and when I use the immediate window to manually check:


    Code
    ? ws.Name Like "TA0632*"


    I get True as an answer, but the code goes on to the next case as though it is false.


    Here is the code I have so far, which I'm sure can be trimmed down loads:




    I appreciate any thoughts as to the best method, or advise as to why my select case is not working as expected.


    Kind regards,
    Averil Pretty

    Re: Lookup Value In Table


    There are various ways you can do this. This might not be the best solution, but I'm having a crack at helping others in this forum where I usually get so much help :)


    You can build it all into one formula like:


    =IF(ISNA(VLOOKUP(A1,Table1,2,FALSE)),"Product not found",VLOOKUP(A1,Table1,2,FALSE))


    Cheers,
    Averil

    Re: Count Number Of Days Occured For Certain Month In Date Column


    Use dynamic named ranges that will expand/contract when you add/remove data. There is a tutorial and there are plenty of posts on using named ranges in this forum. Hover over the word Range to see them.


    Cheers,
    Averil

    Re: Ensure Automatic Calculation


    Hey there,


    Not sure if this makes a difference to you, but the calculation mode is an Excel level setting NOT a workbook level setting, and it is set by the first workbook opened in an Excel session. That is, if you open a workbook whose calculation mode is set to manual, then open your other workbook even if its calculation is technically set to automatic, the calculation in the second workbook will revert back to manual.


    You may also want to use Shift/Ctrl/Alt/F9 which will rebuild dependencies and do a full calculate.


    Cheers,
    Averil

    Re: Remove Spaces From Imported Dates


    Thanks for the correction Dave... I had actually typed a sentance on dates/times as numbers/decimals etc. then decided to just link to your page so cut the majority of it out and left the wrong end part in so jumbled it up - sorry!


    Not sure why there is resistance to find/replace either as it can be done like so (not prettied up just whipped out of a file I've been using):



    Cheers,
    Averil

    Re: Calculate X Percentage Of Cost


    Sorry Dave, I know you know what I meant, but I should have clarified...


    If your initial value/cost is in cell A2, put =A2*1.1 in the cell where you want the calculated value returned.


    A better option if you have lots of rows especially if there is the likliehood of the % amount changing would be to store the percentage (in this case 10%) in say cell A1, then use the formula =A2+(A2*$A$1) where you want the result, and copy it down. This means you wouldn't need to change each formula if the % changes, just the one cell (A1).


    Cheers,