Posts by james8427

  • Count By Criteria & Date

    Re: Count By Criteria & Date


    dear Dave,


    I have created an pivot table in the sorted worksheet and repost an attached, kindly refer.


    I agree with you with the date settings. I have changed it to year instead.


    Now the thing is... I have created a pivot table. The pivot table only counts the number of entries but does not gives me a count distinct value only.


    If you compare the result given by pivot table with what the ideal structure,


    in the year 2005, Marketing dept made 6 purchase entries (2 x entertainment, 2 x food, 2 x repair = 6), that's correct.


    But what i am looking at is the number of different categories they have commited the purchase categories, which is only 3 ( entertainment, food, repair ).


    Hope this gives you a better picture of my problem.


    Thanks alot... =)

  • Count By Criteria & Date

    dear all,


    i have a question here.


    Attachment:
    Exmaple.xls ( Unsorted & Sorted worksheets )


    Explanation of worksheets:
    1. Unsorted worksheet - contains the raw data, which the following heading:
    a. Department - (example: Marketing, Finance, Acccounts, Warehouse etc etc)
    b. MonthYear of Purchase - MMYYYY (example: 012007)
    c. Purchase Category - (example: miscelleneous, food, repair, welfare etc)
    d. Purchase amount - (example: $123.45)


    2. Sorted worksheet - contains the final outcome of my sorting


    Task:
    I need to provide a table, where it would give me an idea of how many different purchase category did the department commit in a particular month and year.


    what i have tried:
    I used pivot tables, and the count. But i realise it must be distinct values, meaning, each pruchase must be different in order to use the count function meaningfully in pivot tables.


    Because i would have an instance where in the month of 022007, marketing dept onli purchase 1 category. How can i do it?


    In SQL... it sounds like:


    Select distinct ( Product Category )
    From Unsorted
    Where Month = Month
    And Dept = Dept.


    Can someone help?


    Thanks..

  • Pivot Table: Display Category Name From Another Worksheet

    Dear all,,


    I have a problem here.
    Kindly refer to atteched spreadsheet.


    In the worksheet, you find 2 worksheet:
    1. Category : Stores the Category code and description
    2. Transaction: Stores the Category Transactional information.


    IN the Transaction spreadsheet, you find 2 pivot.
    1. Default Pivot table view
    2. Desired Pivot table view


    Using the standard Pivot Table wizard, i am able to create that view. Now, i would like my pivot table ("Default Pivot Table") to display like "Desired Pivot Table", with category name.


    1. It has to display the Category Name
    2. It has to display all category names even if there are no transactional information for that category code.


    What i have tried so far:
    In order to display, my only method is to vlookup the code from Transaction worksheet's category code to return category name. I want to find out if there are ways in which i would not touch any formulas/macros.... as my data for each month will be changing, i wanna minimise the truncation of data.


    Pls help...


    thanks!

  • Disable Customize Menus And Menu Bars

    Dear all,


    Scenario:


    I created a customised menu named: Reports
    UNder this menu, the various sub menu is:
    1. Activate Service
    2. Report 1
    3. Report 2


    I would like to ENABLE the Report 1 and Report 2 buttons ONLY after i have clicked on Activate Service button. If not, the buttons Report 1 and Report 2 would be disable... (i.e. cannot click)


    I have no idea how to use VBA to code...


    Anyone can help ?



    Thanks in advance...

  • Sorting Data From Olap Pivot Table

    Dear all,


    i have generated a olap cube and display it using pivot table.
    But i am unable to sort my data from acsending order. For example: I had right clicked on my pivot table > field settings > Advanced > Select "Ascending". The data (sum of quantity) remains unsort after i have checked ok.


    please help.


    cheers
    james

  • Conditional Formatting

    Hi,


    There's a problem that i would need to solve, have been thinking through for a few days.


    I want to do conditional formatting on CELL A1.
    It is a guessing game.


    Lets say, user will key any value from 1-5 in B1.
    Let say the user key in Number 1.


    Cell A1 is supposed to show user's input from B1 (1) if the number matches the mysterious number, it would display in Cell A1 and (2) if it doesnt match, it would highlight the cell A1 and prompt users for the available number left (from example, would be 2,3,4,5).


    Hence, A1 already contain a series of macro-formulas to check for validity of input etc.


    I am confused about the condition formatting, how do i get EXCEL to highlight that conditions?


    It would be easy if i dun use macro formulas, but i have to use them.


    I tried using conditional formatting, with Input of Formula, but... it would only highlight if the cell value is TRUE...


    and because it is embedded with so much formulas... i cant seem to just return true or false.


    Help...


    Rdgs
    James


    How do i do conditional formatting to highlight Cell A1 is

  • Formatting of data

    Dear all,
    i have a question.


    In my attached file, there are 2 worksheets; namely "DATA" & "BREAKDOWN".


    I have a series of data in my WOrksheet "DATA"
    I need to have a breakdown of these data, into WOrksheet "BREAKDOWN", with the ability of the user to specify which category of data to generate, refer to my "BREAKDOWN" worksheet for a view.


    the criteria is for me to click the generate button in the "BREAKDOWN" worksheet and the data would be generated based on the data in the "DATA" worksheet, and everytime it generates, it will includes any new data updates.


    bear in mind that the data is going to be used for 7 days 24 hrs.. and the data from the last hour would piled up continuously.. hence, the data in the "BREAKDOWN" would gradually build up as well.



    can anyone help me with the coding?


    rdgs
    james :cool:

  • Trends & forecasts using Excel

    Hi all


    I have a question.
    I had used Excel using its macro and VBA to help my colleagues automate data reporting. But i would like to know, if excel is capable of proposing trends/meaningful data to user based on user's requirements and current data?


    Example: I have historical forecast shipments. I wanna system to propose my production based on (a) ideal next month forecast (b) targetted shipments for next month.


    The program would actually promt the user of the input requirements, as per (a) and (b). With these 2 inputs from users, the program may actually work on the logics at teh back end with VBA, to proposed the ideal production units for the company.


    I am wondering if this is feasible?


    Recently, i was reading through DATAWAREHOUSING by HUMPHRIES HAWKINS DY. In it, it included a datawarehouse in excel? how is it possible?


    :thanx: hope to hear from you guys soon. cheers.

  • VBA : Need to retrieve data from database in Online Server

    Hi all,


    I have an online database, in Access 2000 format, Stock. mdb.


    Task:
    i need to download the data in the database into MS EXCEL, MAKING use of MS EXCEL as an interface and also analyze the data in pivot table. (everytime i click on update button from the Excel Interface, the data would be downloaded from the online server into the Excel offline)


    First of all, how do i create that link from Online server to my MS Excel interface? Secondly, do i need to create a so called "cube" to contain all the data in MS Excel?


    PLease help me..

  • Access : Tables Need to store images

    Hi all


    I have a question for Access Database.


    I need to build a database for books.
    I have ISBN numbers and other details, and i want to have the images of the books inside.


    Supposed i have several books, how could i insert images into Access database?

  • Access: VBA Need to open up file

    Hi all!!!!!!!!!


    i have a question! Important... need it urgently..


    I have a file : 123.vb


    I would like to link to a command button on my main switch board, and when i click, 123.vb would be executed...


    hOW?



    rdgs
    james