Posts by bearcub

    Within the last couple of hours I have noticed that my cursor starts scrolling to the right without my touching the keyboard. Or I will be pointing at a cell and it starts scrolling to the right without the tab key being pressed.


    Does anyone know how to prevent this from happening or turn this option off? It is quit irritating and slows down the work I have to do.


    Thanks

    Is there a way of attaching an email from Outlook into Excel. A co-worker mentioned she used to do that where you clicked on the cell and it will open an email message from Outlook. It behaves like a Hyperlink but it isn't a hyperlink.

    I have a situation where I need to count the unique for a customer. The areas to be counted are separated by blank lines.


    At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements.


    Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?


    Thank you in advance for your help.


    PS I had to take out the Bolded values and delete some lines to make have the appropriate file size. The length of the file is normally around 1073 lines.

    Files

    My company recent installed Excel 2003 on our computers. I use pivot tables extensively and been unable to find the "Show Pages" option when I right click inside the pivot table.


    Has this feature been discontinued or is it called something else? Do I have to have it installed or is it in the Options menu



    Thanks

    Re: Trouble with Percentage field


    Thank you for the Help suggestion. I went into help to find out the problem but I didn't check field size.


    I inadvertantly sent this question twice because I thought that I set the first one to the Excel help section.

    I created a database with a percentage field.


    when I type in .3 I would expect the field to display 30% correct? the format in the table is number=>percent=>2 decimal places.


    when I type in .3 the display is "0.00%". How do I format this field to display the correct percentage.


    thanks for you help in advance

    I created an Access database. I have a percentage field in the table which statements the amount of discount the customer received on our company products.


    the problem is this


    If we want to give the customer a 30% discount we should be able to type in .3 and it should display 30%. Instead, it is displaying 0.00%.


    If you type in 30 it displays 300%.


    What do I have to do to correct this.


    Thank you in advance

    Re: Sort Macro


    I first was trying to delete the row and forgot to include the column argument (Cells(i,1)). After that, I did run a macro to do the sort.


    Thanks

    I have a sample sheet from a larger file that I have to sort monthly.


    The raw data is the basis for billing the customer. This sheet feeds a summary sheet which is then sent to the customer for payment.


    I need to sort the sheet by Agent, Interval and Type. Agent is the specific product that they are using; interval is the portion of an hour that they it updated. Type is the number of web pages they are seeing on their browser.


    I also want to have the SubTotal and number of day columns underneath each agent type as well.


    I was trying to create macro but soon realized that it was beyond my grasp at this time.

    Re: Macro for Summing Date Column


    it is raw data. However, this sheet is going out to the customer as an invoice to be paid. So a pivot table won't work.


    The formatting is not allowed to change.

    I have a spreadsheets that are for billing customers. We bill them by the totals days they use our products (see Column G "Days" in the file).


    Instead of manually adding each customer (I have 20-30 customers to do) I want to create a macro that will sum all the products.


    Between each product are two blank rows. On the first blank row is the text "Subtotal". All other cells on that row are blank. I was thinking of using a do until loop inside a For next loop to add the days for each product group.


    However, I haven't the foggiest idea how to put it together. Can anyone help me?


    Thank you for your help.

    I am using the SUBTOTAL function in two different cells. Column J of the spreadsheet contains revenues. In Cell A1 I have this function(=SUBTOTAL(9,J15:J669). Column A displays the calendar month. In Cell B1 I am using this function (=SUBTOTAL(1,A15:A669).


    When I filter on a particular month I will get the visible month (May05 for example) in B1 and in the A1 I will get the monthly total.


    A1 = 1,000
    B1 = May

    However, when the filter is off, B1 displays Jan-05 since all the months from Oct to May are visible (our fiscal year is from Oct to Sept).



    However, when the filter is off the cell displays Jan-05. Is this because it is the default. because more than one month is being shown. Is there a way around this, perhaps by using another function?




    Thanks

    Re: Linking cells from one sheet to another


    I don't quite understand what you mean by using the names. Are you suggesting creating a cell name for each person and using this in the formulas.? Like Offset("Joe Blow", 2,3)? to reference two rows below and three columns to the left?

    Re: Linking cells from one sheet to another


    Yes, I am extracting information from the detail sheet to another. I will monthly update the detail sheet and I want it to populate the the Summary sheet.


    You mentioned defining names, are you suggesting have a range name for each column. The saleman data is all the same, quota, month revenue, percentages, etc.


    I could point and link but I think there might be a more efficient way of doing this. I don't want to lose the cell references.


    Thanks

    Re: Linking cells from one sheet to another (Revised)


    It has come to my attention that no one knew what I was asking.


    I have revised the file with arrows pointing at the cells I need linked. The bottom section is to linked to the top section (both on separate sheets).


    Is there someway to use a find or match attached to Joe Blow so that if any columns and rows are added it would still match?


    Thanks

    Files

    • example.xls

      (25.09 kB, downloaded 137 times, last: )

    I am creating a file that summarizes commissions paid for the entire year.


    One sheet (the top portion of the file) contains all salemen detail (quota, payout rates, revenue, adjustments, bonuses, etc.)


    The bottom part of the sheet is the destination sheet (where I would put the formulas). What kind of formulas (Index, offset, Match, Choose, etc) would I use to pull the information from the detail sheet? At present, I am using the index function to do this. My main concern is that at the year progresses, rows may be added which may affect the links. This is why I was thinking about using the match function but don't know how to use it in this information.


    Vlookup I feel won't work because of the table structure.


    Thanks

    Files

    • example.xls

      (19.97 kB, downloaded 122 times, last: )