Replies

Steve, this is sweet music and perfect. GREAT JOB and THANKS!

Perfect!

Thanks.

Ok… did and unchecked the box but still showing totals at top. Do I have to refresh (tried) or what to update?

I right click in one of the data fields and it brings up a menu that contains “value field settings.” This is the only thing close to what you say above.

When I do, it only has 2 tabs:
1. Summarize values by
2. Show values as

I am doing something wrong???

Rory, the path you describe doesn’t work in 2010. Other ideas welcome.

yep. I missed this before. Thanks.

Perfect!!!!!!!!
Thanks.

Have you tried using styles? That is what I would do for myself, make styles for each special format and use them instead of directly applied formatting.

“Styles.” Can you provide an example?

That’s right – only myself. The reviewer of this has very precise standards. 2 decimal places, dollar signs on the first row and last row, conditional formatted such that less than zero is red, etc. If text is copied and pasted or a formula is entered in row 1 (dollar sign), and then dragged down to rows below the dollar sigh follows. As such, I ‘d like to lock down just/onlyl the formatting.

Apparently, this isn’t possible?

I just want to be sure I am actually answering your question. As I understand it.

You wish to protect cells from formatting changes only. The user should still be able to edit content.

If the above is your question the answer is no. Once cells are protected, their contents can not be changed. Formatting can be enabled on protected cells (the reverse of what you want).

Can you give me more information about the situation. Perhaps there is a better strategy.

Yes –
I have spreadsheet that is formatted a certain way. I update the numbers each month and add columns or rows, but I don’t want any of the row/column formats, for the selected cells, to change.

It is attached on my original post…

I’m not sure what you mean by “When I copy and paste into Word it comes in and doesn’t fully utilize all of the page.”
The word document that you attached contains two pictures, which very nearly fill the page, and there is only one page. I don’t see how we can fix this for you!

Can you post the word document that you are having problems with.

As you say links work “on click” only in show mode. If you need to check the link Right click on it > open hyperlink in edit mode

I do not need to check the link. I want the link to redirect the screen to the location associated with the hyperlink URL. It only works in “slide show” view. If I send as an email, would like the link to work outside of slide show so if user opens up, link will work without having to go to slide show mode…

Thanks.

Hans, I was able to get it to work also on the smaller subset of data I posted. When I try to do it on the full data set, it never finishes and takes up 100% of the CPU for hours…

I see your comment below, “BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.” Maybe this would fix the problem with the full data set.

How do I do what you are referring to?

Mitch

Click on Jan LE or Mar LE (or in one of the row fields)
Select Pivot Table | Formulas | Calculated Item from the Pivot Table toolbar.
Type a name for the field, e.g. Variance.
Edit the formula so that it consists only of =
Select Jan LE item in the list ot items, then click Add Item.
Type – after it in the formula.
Select Mar LE item in the list ot items, then click Add Item.
The formula should now read =’Jan LE’-‘Mar LE’
Click Add, then click OK

BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.

See the attached version.

Attached is the file I’d like to do the math on…

Mitch

I don’t know enough about your workbook.

Hans, it looks like I asked this same thing back on July 25, 2008. Below is your reply. I am trying a similar approach to this table, but it locks up my PC taking up all the CPU. I let it run for an hour and it was finished but didn’t show a result…

“Sorry, I interpreted your “I have two sets of columnar data” in a different way than you intended it. What you need is a calculated item instead of a calculated formula.
– Click in one of the values of version.
– From the Pivot Table toolbar, select Pivot Table | Formulas | Calculated Item…
– Enter a name for the formula in the box at the top.
– Enter the following formula in the formula box:

=versions[2008 OP]-versions[Sept LE]

– Make sure that you use the names exactly as they are in the data.
– Click Add, then OK. ”

Yes, in the sense that it can’t be done in the pivot table as far as I know. You could add formulas to the right of the pivot table to calculate the difference, but they wouldn’t be updated correctly if you changed the layout of the pivot table.