Replies

Legare,

Thank you very much. I see how you approached this problem and I am impressed. Your solution works perfectly for me.

Regards…

Yes, I finally found it. Gremlins had it kidnapped there for a while. I even found it in Excel 2000.

All I had to do was really look…

Thanks.

Well, d-u-h-h , I looked right at it in both 2000 and XP and it just didn’t register. And in all the times I have looked at that dialog box, that option has escaped my notice.

Thanks for your explicit and courteour directions.

I’m running Excel XP on a Windows XP workstation, and I couldn’t find Enable automatic percent entry. In fact, I can’t ever recall seeing that option in any version of Excel. Have I been missing something?

Well thank you for the kind words. Of the many things I am called, “brilliant” is not often among them. But feel free to pick my brain on pivot tables. As it happens, I do a lot of work with very complex pivot tables for a lengthy project I have been involved with.

You can also name the region and update your table using the named region by refreshing it.

Here’s an example where I named my pivot table “Regional Summary” and I named the database “PivotDataBase”. Each time there is a change to the data, you could simply click a macro button that runs the following code:

With ActiveSheet.PivotTables(“Regional Summary”)
.PivotTableWizard SourceType:=xlDatabase, SourceData:=”PivotDataBase”
.RefreshTable
End With

This will update your pivot table with the new data.

Hope this helps.

Steve,

I was going to respond in a similar fashion to Melanie’s problem, but my response would not have been as elegant as yours. Yours looks like it fits the bill nicely.

George

Colin, I’m sorry I didn’t get back to you more timely, but I followed your suggestion and got the results I wanted.

Thanks…

I might not have the full picture of your problem, but the first approach to me would be to create a table where you enter each person’s name (in column A, say), then create a column for each other item of information. For example, column B would be Enrolled?; column C would be Responding?; Column D would be Question 1; Column E would be Question 2 and so on. Then enter a “1” (no quotes) in each cell for each person that fits the category. So, for example, if Person 3 were in cell A4 and answered Questions 1, 2 and 3, then you would put a 1 in cells D4, E4 and F4.

At the bottom of each category column, you could use a SUM function to total up the number of people who responded in each category and then compute the averages. So, for example, you have 50 people enrolled, and your SUM for Question 1 is 28. You divide 28 by 50 and format the result as a percentage. In this example, you would report that 56% of the people responded to Question 1.

Assuming I interpreted your conditions correctly, this is how I’d start…

Excel uses the single quote as an indicator that whatever is in the cell is text, so when you enter, for example, ’02, Excel displays only “02” in the cell and treats it as text. However, if you use the actual apostrophe key (on my keyboard it is the key to the left of the 1 key and above the Tab key), the apostrophe will appear in the cell. Hope this is what you were looking for…

Thank you, John (and all the others who so kindly replied to my post). I will see if your suggestion is feasible. It looks good to me, but I am far from the final say on this issue.

Thanks again.

The problem, though, is that this is a debt model and there are always circular refs in such a model, so nothing can be done about taking them out. That seems to mean that if a particular user changes an assumption cell so that he/she gets the dreaded Divide By Zero error, there is no way to recalc that error outside of visiting the affected cells individually. It looks like I will need to create a macro that searches for Divide By Zero errors and recalcs them when the macro is executed. Can’t seem to find a better way.

So do you think that if the sheet had no circular references this phenomenon of the Divide By Zero errors not recalcing would not occur?

I have attached a non-specific example of the problem. If you got to C9 and delete the contents, you will notice all the Divide By Zero errors that appear. If you then do a CTRL+Z, you will note that the Divide By Zero errors do not disappear. If you go then to cell C27, press F2 and then Return, you will see the errors for that cell and its dependent cells be replaced by values.

I did consider that as an approach, but this workbook has been distributed to a number of others and it would probably not be possible to bring them all up to date. In fact, that’s part of the overall scope of the problem: other users who understand the native Excel formulas now in the workbook would have considerably more difficulty in understanding custom functions or formulas that trap for the error.