Posts by yjoshi

    Re: Skipping Cells To Add Data

    Its not clear how are you posting the data to the table? is it via VBA code?
    While you are posting the data, you can use named range or other VBA methods to identify the last filled cell and enter the new data into the next row.

    Please attach the worksheet or paste your code and we can help.

    This is a very simple function, so don't worry it will be solved in minutes if you give right information.

    Re: Custom Date Series Class Schedule

    I tried an approach comfortable to me.. that is range name.

    1. I entered date with Monday (3/24) in A1
    2. While i was in cell A2, i created a range name (Menu -> Insert -> Name -> Create)
    with name = mon_wed and refers to =
    3. Now from A2 to A10 i just entered formula =mon_wed
    (actually i just pressed F3 and selected the range name)... bang... i am done

    Hope this helps

    Edit: I have attached a worksheet for your testing.. hope this helps[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]BTW, you can easily make edits to the formula in the range name to get the desired results as mentioned in your thread... its just left to when you add how many days :)

    Re: Lookup Based On Column & Row Criteria

    Need more details... also as per the forum guidelines, please don't assume the answer!!!

    I would recommend Pivot table (if the data is spread across worksheets, i will consolidate using pivot table consolidation and then pull out reports based on the same.

    If you can provide a sample dummy worksheet we can solve it quickly

    Re: Return Text Based On Cell Percentage

    I interpreted the question differently than Dave... not sure if this helps
    I thought you are asking how to change text based on cell value in percentage...

    [frc]=IF(A1<0.3,"Less than 30%",IF(A1<0.6,"Less than 60%","Greater than 60%"))[/frc]

    Re: Consolidate data from multiple sheets into 1

    Firstly I will suggest, organize your data in a bit better manner.

    Your cost center sheets has too much data that is not required....
    like quarterly results, subtotals... etc.

    You can still take this and write a code to delete the unwanted data (rows / columns) at one go.

    Next... you can use the pivot table feature of "multiple consolidation ranges".

    I tried it and almost got what is required... you will need to copy the pivot table paste as value and do the additional formatting... which should not take much time.

    See the file attached...

    Re: Search for values

    I know you are expecting a vba code help... but lets try to think out of box for a moment....

    have you used the google desktop utility?

    If you do not intend to do any updates programatically and just open these files.... i think this is best... i tried doing it....

    gave 10000 .xls in the search criteria and it searched about 28 files from my pc...

    I know the VBA way of searching is still pending... will try to put the code tomorrow.. had worked on something like this previously... but its good if this helps you :)

    Re: Disabling automatic conversion of X-X copied data into date in Excel

    Not an exact answer to your question, but this can be a good workaround...

    See if it helps you.

    Try importing the webpage as data -> get external data -> New Web Query:
    1. Enter the url
    2. select tables only
    3. select full html formatting

    and say OK.

    You will get the entire data as it is.

    I tried your link. Have uploaded the sheet... let me know if it was of any use.

    Re: Test for empty cell

    The problem is that, when you erase data with space bar, the value is not "" but is " " includes spaces. so good way is to trim the spaces and try again.

    It worked for me... let me know if it works for you.

    could repruduce your problem....

    I have a solution that works ...

    1. Select the range in excel
    2. Press Shift Key and Open the Edit Menu
    3. You will see "Copy Picture" ...
    4. Select bitmap in the new window(type)
    5. Open word and say paste.... it works

    Do you want to just convert it to degrees C or even have operations on that?

    I will suggest using format to do this.
    Follow the steps....
    1. Select the range where you want the format, and which contain numbers
    2. Menu -> Format -> Cells (OR press Ctrl + 1)
    3. Select Number Tab
    4. Select Catagroy = Custom
    5. in Type ... enter General" then press Alt + 0176 and leave the Alt button.
    You will see the degree sign. Now enter C"
    in short create format of General"<degree sign>C"
    6. Close the format window.

    You will see that all the numbers are formatted to Degree Celcius....

    You can also do operations on the same, or even create graphs out of this data.

    See the sheet attached.

    I think your question was a bit vague .. thats why no replies.

    I have tried to answer it .. see if this helps.

    Also see the live demo.. file attachedl

    You will always see it only when you click on the cell drop down.

    By default, if you appliy a validation to a blank cell... you will see a blank cell... and only when you click on drop down (or press Alt + down key) you will see a list.

    In case you are seeing a value, you have probably entered it.

    Try pressing delete... your value will vanish and you will see list only when you want it ! :)

    You can do this by using New Database Query, using excel file.

    I tried it with 6 sheets Jan -> Jun and it did work for me.

    I have recorded the macro of the procedure...

    The key here is to change the query to "SELECT * from ...... Union All Select * from .... "

    see the code below......

    As told earlier, this is the code generated automatically.... if you want you can also try recording one and then modify as you wish :)

    Hope this helps. Let me know if this does not work.


    I think I know what you exactly want.

    You have 4 activities and you want to have a stopwatch for each one to start and stop the things.

    Also you must be interested in making analysis and good charts out of it.

    I have tried whatever i could think you want.

    Please see the zip file and let me know.

    BTW .. Thanks for your query... it was a good exercise.

    Deniel (Derk),

    I uploaded my sheet without seeing your update.
    Actually this was eating my head whole night and had thought of the solution at night as well. I just implemented it and uploaded without seeing yours.

    Anyway.... both of us are on similar lines, with minor adjustment in techniques.