Posts by JimFuller1

    Re: math averaging


    When computing average change you are faced with a dilemma. Do you want to consider the average change to be the amount of change you averaged over some number of years? Or, do you want to know the amount of change that would produce your current year given the year you started?

    It's a personal choice in my opinion. Sometimes one is better than the other. Sometimes neither is worth anything.

    Take a look at the attached and it may become more clear.


    Re: Saving Charts and Removing the Data Source


    The short answer is no, I think. I know that an Excel Chart will always require underlying data. I know you can move charts to other files. But, as far as I know, you always have to have data. Somewhere. Even in another file. But there is always data somewhere.

    You can copy it to the clipboard and paste it into Paint to capture a static picture. But, that doesn't give you something that you can edit later. It's just a picture. No underlying data.


    Re: inserting hyperlink to spreadsheet works but macro won't follow it

    lol mud,

    Ok, here is a path that uses the alias of Drive H.


    Here is a path that uses the official path to the same file.


    Both lead to the same place. If one of the users has a drive mapped to something you don't have on your computer, Excel will be confused.



    Re: Best way to isolate subtotals


    My approach would be a little different. I would set the sheet to display all the rows. I would then put following formula in any blank cell in column A.

    For cell A10, if it is blank, the formula would be =A9
    For cell B10, if it is blank, the formula would be =B9
    And so on.

    That gets the data back where you want it. I would then select the entire area and [copy][paste special][values] to trap the subtotals for the next step. Sort by column D. Delete everything that you don't want.


    Re: inserting hyperlink to spreadsheet works but macro won't follow it


    My first suspicion would be that the link coming through the user form has an alias for the drive letter and not the official path name.


    Re: Count or SumProduct, don't know which


    When I have a similar problem, I use the [Data][Filter][Advanced Filter...] command and copy unique records to a different location and then just look how many rows it takes.

    If you need to know at any time how many there are and you don't have time for that, you'll need a formula.


    Re: Conditional Counting


    The problem is with the named range "Myrange". It is not covering the last two dates. The reason is that there needs to be a way to "count" how many rows are being used. Column B is not a good candidate because it has blanks. Column A could work if multiplied by two. Or, any column that will have no blank cells. Is there going to be a column like that?


    Re: Vlookup on Subtotals


    Try the SUMIF(range,criteria,sum_range) formula. Use the part number on sheet 1 as the criteria, the range and sum_range will be on sheet two. Select the whole range ignoring the fact you will select some of the subtotals.

    Don't forget absolute references when you copy down.


    Re: Transfering Chart from one sheet to another


    While I don't have any code to offer, maybe this will help as a strategy.

    1) The new sheet name has to be captured in order to feed into a chart data renaming macro. That is usually easiest when you manually activate the sheet the chart is on by clicking on it. Then you execute a data range defining macro that has statements about the data ranges within the active sheet/chart.

    2) You already know what names to assign to each data element because you will have identical names as the sheet you copied and I assume they won't change.

    3) Write the code to execute on the active sheet/chart and make statements about the data ranges, label ranges, etc.

    4) Select the sheets manually or programatically step through all sheets and execute the data range defining macro.

    Hope that helps,

    Re: Transfering Chart from one sheet to another


    Try copying the sheet itself and that will give you a new sheet with the chart included. The range names in Excel for the new sheet will refer to that sheet but look the same when you look at [Insert][Name][Define]. Don't worry about that. Right click on the chart and select "Source Data...". The Series are already named properly you just need to update the Refers To portion of the formula. Be careful to use the newly created sheet name in the range name and it should work.


    Re: Matching records with VBA


    I'm not suggesting you change Sheet1 or Sheet2. I think they are the outputs. Right?

    But, what about adding a sheet(s) that you use to parse the incoming data that causes all the trouble and then put it all back together with a much simpler formula on Sheet1 or Sheet2?

    Would that help?


    Re: Matching records with VBA

    If they are only interested in the output, you may have options. Have you thought about parsing the data on sheet 2 before you begin? That will allow you to shorten that formula considerably.


    Re: Matching records with VBA


    Are you married to this data layout? Because, I've got a hunch that the complexity is caused by the way the data is organized. If you want me to take a shot at simplifying the problem, I will. Sorry, I can't help with such little information though.


    Re: Scroll through visible cells after autofilter


    If you mean without VBA, try this.

    Select all the cells that are in the filtered list and use the following menu commands.

    [EDIT] [GoTo...] [Special...] [Visible Cells Only]

    After doing this, you will only have the visible cells selected. You can hit the [Enter] key and your cursor will advance to the next visible cell.

    If you need the code, record the above steps, I don't have anything handy. Sorry.

    Hope it helps.


    Re: Range of Graph


    Andy has given you two ranges that automatically resize so you may not need to know how many rows and columns you have.

    Also, I've found that a large dynamic range can slow down a spreadsheet considerably. If you have a large spreadsheet and just want to know how to automatically add a new coulumn try this. Position your cursor in the column to the left of the last column in the graph. Insert a column. Copy the last column to the new column. Delete the info in the last column by hitting the delete key.

    Even better, create the spreadsheet with all the columns you will need and hide the columns you aren't ready to use. Excel will not show them on the graph.


    Re: append to cell

    You won't be able to put that formula into cell F3. You will have to put the formula somewhere else. You would need a macro if you want to replace F3 with the new sentence. Assuming your two sentences are in A1 and A2:

    Sub combine()
    Range("A1") = Range("A1") + " " + Range("A2")
    End Sub