Re: math averaging
bnelson,
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.
Jim
Re: Saving Charts and Removing the Data Source
sassy,
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.
Jim
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.
H:\Folder1\Folder2\File.xls
Here is a path that uses the official path to the same file.
\\YourName\Folder1\Folder2\File.xls
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.
Better?
Jim
Re: Best way to isolate subtotals
carlmack,
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.
Jim
Re: inserting hyperlink to spreadsheet works but macro won't follow it
clearasmud,
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.
Jim
Re: Count or SumProduct, don't know which
Jamie,
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.
Jim
Re: Conditional Counting
The workaround would be to put something in the blank cells in column B and adjust the formula.
Jim
Re: Conditional Counting
Speluchi,
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?
Jim
Re: Vlookup on Subtotals
Rick,
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.
Jim
Re: Matching records with VBA
Hi Helen,
Sorry to be late but, just for the record I attach a sample based on your sample that will do with formulae what the macro does. Depending on the situation... oh who am I kidding, it's hard to beat a sexy macro.
Here it is anyway. Good look with your determined users. 
Jim
Re: Transfering Chart from one sheet to another
Malhar,
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,
Jim
Re: Transfering Chart from one sheet to another
Malhar,
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.
Jim
Re: Matching records with VBA
Helen,
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?
Jim
Re: Can't insert row; warning makes no sense!
QMDave,
Try looking for a formula that references the last cell in the worksheet. Also, check named ranges that may have been corrupted. Don't forget about hidden rows and columns when you check.
What happens if you do [Control][End]?
If all else fails, post the worksheet here and I'll see what I can see.
Jim
Re: Race track chart
You might want to check out chart type "Doughnut" that comes standard with Excel.
Jim
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.
Jim
Re: Matching records with VBA
helenpaps,
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.
Jim
Re: Scroll through visible cells after autofilter
facruz,
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.
Jim
Re: Range of Graph
basicuser,
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.
Jim
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