Posts by benlauhh

  • Sort the dates and time based on the data entered via userform

    Hi all!


    Specs:
    1 workbook = 1 year planner
    1 worksheet = 1 month's schedule, organzied in weeks
    Based on my (unworthy :) userform, i will then enter the relevant data in.


    Question:
    As the dates and time entered will not be in any order, how then can i sort them accordingly in descending order (by dates, then time) automatically as i key in any new entries as i've listed in worksheet "Preferred" as an example.
    (Macro not displayed yet, view code in VB Editor)


    Also, how can i use "View Schedule" button effectively by prompting for a particular week of the month (& year), and either popup the respective week entered or just bring up the respective worksheet.


    Sorry i just learned VBA for excel, so it may be less of a challenge for many of yall. Many thanks guys for even viewing my problem.
    Kudos to the people of OzGrid! you guys rock!!

  • Validate dates entered in cells

    hi Mahesh,
    Would love to do an exampe for you, but kinda bz with my own stuff.
    But thought i might offer some pointers to help you get started:
    1) Create a column for Month, and goto > Data>Validation> under Settings, choose List. This allows you to choose the month using a drop-down box. Same method for year. there is an error tab you can choose to inform users they have chosen something other than what is given in the list.
    2)Use IF statement to check against month selected, and then display all dates for the month in rows.
    this method is just one of many, im sure not the best, sorry if i cant be of any help


    Cheers!

  • How increment numbers

    Hi Marc,
    If you can be more specific about where you want to place the result?
    I assume it to be how u mention it,
    This number is 1
    This number is 2
    This number is 3


    I would use a simple formula like this:(assumption: A1 is already declared as '1', you only want to add '1' to every other row)
    =IF(A1<>1,A1+1,A1+1)
    add the above formula on A2, then drag the box, to the rest of the rows that you want, they should add nicely.
    Hope it helps?


    Cheers!

  • Controls (Forms Toolbar): Build a form that with &#039;submi

    Hi Midas, i'n not sure if im helping you by saying that there is a 'Form' layout under the 'Data' Tab. However you will have create the necessary fields 1st, with a sample entry 1st. Subsequently, just highlight the required fields and click Form. You will then be able to key in the data needed manually, using Tab to go to the next fields, and Enter to complete an entry. It will clear to allow you to enter the next set of data. You can also use Criteria to search for any relevant stuff you need.
    Hope it helps?

  • [Solved] Formulas: adding cells if other cells match...

    In sheet 3 A1, where you would want to indicate the number of times a name occurs, type this simple formula:=COUNTIF(Sheet1!A1:A5, Sheet3!C1) + COUNTIF(Sheet2!A1:A5, Sheet3!C1).</n&gt;
    Where Sheet 1 A1~A5 & Sheet 2 A1~A5 contains the names of people and Sheet 3 C1 allows you to type in who evers name you wanna see. You can then manipulate the formula variables to suit your needs.:) Hope it helps!

  • Formulas: How to Compare, Extract and Replace?

    Hi Thomach! Thanks for the reply, unfortunately that is not what i had in mind. And i wouldn't want to use VBA or macros to do it, coz i'm always having some kinda macro virus/problem.
    You see, i not only need to keep track of the PO used, in fact i need to know when they are being used. But that i can figure out if i can get the appropriate PO out. If i use (=VLOOKUP(B2,A2:A101,1,FALSE) on column D with respect to your list, and drag them down the column, you will find that if i have anything not matching (like just plain text) under "used PO" column, i will get a lotta unwanted #NA blanks in between. Is there anyway i can disregard non-matches, and just fill in the used PO numbers down the column?

  • Formulas: How to Compare, Extract and Replace?

    Hi all.
    </p&gt;I have a coluumn containing some text, and some numbers. I made a sheet containing a list of PO numbers. It looks like this: POA ######
    </p&gt;What i intend to do is, Have a register whereby i can track the PO numbers used, in comparison with the list i made. So it will be something like Comparing whole coluumn with the listing, once matched, extracted that number from the listing and replace it with a blank.
    </p&gt;I've tried using VLOOKUP, it can only help me match the exact with respect to their rows allocated. Hence i get alot of unwanted space in between. I guess i can replace the number extracted with an IF statement using " ".
    </p&gt; Pls HELP!