Replies

That is correct. (attached sample worksheet “CONDITIONAL FORMATING WITH IN A FORMULA CELL AX.xlsx)
If the formula cells in column D (D5 thru D16)has a COLORED DOT ICON (preferable) or the cell fills in with a color if greater than or less than a
predetermined criteria based on the percentage difference from the total (cell D4). If possible, since Cells in D10 thru D12 have no values in the “#” or “CASH $” no conditional formatting is
required.

The conditional formatting is to be completed only in Column D. Hopefully this is possible.

i would appreciate your upload.

Thank you.

I hope I got it right. Included are two options, one with cells filled conditionally and other using stack chart. hope it will help.
see attached. 33279-Copy-of-CONDITIONAL-FORMATING-WITH-IN-A-FORMULA-CELL-AX

if I understand this correctly you don’t want to change cell’s appearance but want some sort of object to change color depending on cell value instead? if yes you can do this using stack chart. let me know if this is what you require and I will upload example for you.

Thank you for this, much appreciated. It will save me a lot of fiddling.
I am quite surprised that the codes look soooo different from each other, especially the overwrite part. Anyway, great stuff. Thanks

Hi Roy,

This is absolutely fantastic! Thank you very much!

Can I be a bit cheeky and ask for a slight addition to this code? Can it also delete rows where column D reads either “EAA” or “TOC” please?

Thanks

Never ever have I seen or heard about windows update causing such issue. I would advise you to check for some kind of cheeky vba code that perhaps embedded itself into your excel and is closing the application. Also, do scan your machine with antivirus – just in case.

have you checked your axis options? are they auto or fixed? this might just be the problem here

Hi,

From what I know this error message appears only when link reference no longer exists. Misleading in here is that when you open the source file first, the error message does not appear, but dont be fulled – the link remains invalid. I would try to find the the invalid link first. Matter is slightly complicated when link refers to named range, but… you can still find it.

I would start with searching for exclamation mark in the entire workbook. Open both sheets, go to the “new sheet” as you call it, “Find”, click options, in dropdown to the left change sheet into workbook and click find all. In search results look for #NAME? under “value” (or any error value for that matter).

If link does not refer to named range but to a cell instead, repeat the above steps searching for [ (square bracket) instead of !

if this doesn’t work then check source data in your charts if you have any in your sheet.

hope this will help

=ROUND(J22*1.1,0)
=ROUND(J26*1.1,0)

and format your cells to show no decimal places

you can also use ROUNDUP(rounds number away from zero) or ROUNDDOWN (rounds number towards zero) in this syntax.

🙂
happy to help

I have a spreadsheet that contains data that I downloaded from a provider. One of the columns is a date/time format 12/20/2012 10:08 AM.

I am trying to execute a Microsoft Query to return information within a specified date range (12/20/2012 and 01/19/2013). When I use the “between” function, I get zero results, when I use the “greater than function”, I get completely wrong results.

Any suggestions?

Just a blind shot coz I don’t see data. Try with =left(,10) then format formula result as date.

This is proving to be quite a challenge.

For some reason it does not like date format. Here’s how I managed to work around it, follow the steps below and it should work – hopefully
1. Insert column between Q_date and Staying_here call it lets say Q_date2
2. insert the following formula in cell D2 =TEXT(C2,”mm/dd/yy”) and copy it down
3. update named range q_date to =QuestionaireData!$D$1:$D$7
4. refresh PTs
5. change filter field in all PTs from Q_date to Q_date2
6. update VBA to refer to Q_date2

it now should work

as for extra line of code for the new PT, check first if your new PT is indeed PT3:
1. right click on PT
2. PT options
3. check top of the pop-up window

hope this helps

OK, in that case let’s leave this thread if you’re happy with the ideas you’ve got for your original problem, and keep updates to the other thread. If the guy who raised the original request is still having problems I’ll have another look and put any updates in his thread!

sure thing

Had a chat about this code with some clever clogs but it seems that the matter is more complicated than I thought. Try not to filter pivots manually and it should work just right. See v3 of your spreadsheet, hope it has all you need. You will find the code now and will be able to edit it. Will let you know should I find anything that could improve this, otherwise- Good luck.32989-PT_1_v3

Do you need to show different (increased by 10%) figures anywhere else? C33 or J33 perhaps? if not here is the spreadsheet with some simple formulas in indicated cells. Let me know if you need anything more sophisticated. Happy to help.32990-Copy-of-Sample-contract

So were there still problems with the Copy2 version you posted yesterday? I was actually lurking in that thread for a while but I thought you’d managed to sort the problem 🙂

I’ve never actually tried to program the CurrentPage setting of a PivotTable before, but a quick google shows that you are not alone having problems with this feature!

Well… Copy 2 was a bit of a hit and miss I think. I thought that I fixed the validation issue as it worked for me but am not sure if it worked for him though. Changing the code from macro to selection change backfired completely. I’m thinking that this could work for him as long as he is not trying to manipulate pivots in other way than changing cell A1 AND the code is triggered manually (button or something). I wanted to error proof it somehow but “On Error Resume Next” doesn’t seem to be the option here, it just gets funny. No idea how to solve this, but if others are having same problem then maybe it is indeed supercomplicated or impossible?