Posts by Box Jockey

    Trying to apply conditional formatting to a pivot based on the ranking of the value. It was working for most values but some would generate an #N/A. I built some test formulas outside of the pivot and I am seeing the results in the pic below.


    Both rank formulas are as follows:

    =RANK(E31,Scoring!$H$10:$J$10,0)

    =RANK(E32,Scoring!$H$10:$J$10,0)


    The sumif value is coming from the range used for ranking, and it is looking at the same dataset as the pivot table (it will be in cell H10 of the ranking range listed above). I thought maybe there was some weird rounding or decimal issue, but based on the variance in the pic it looks like they are identical too me. Not to mention it is working for most of the other cells. Any ideas what could be driving that?

    We have a winner, thank you!! :D


    So I thought all our installs were pretty consistent across the company. At least we are all on 365. Are there different versions within that which can cause this issue?

    The following code is taking row headers that appear as full month names and converts them to 3 letter months. Works fine on my machine and many others, but for a couple users it corrects the first (January to Jan) and then repeats that across all months so everything becomes Jan. Unfortunately I don't have access to their machines to test, any ideas what I might be doing wrong here?


    Code
    Set rFixRange = .Range("E" & iTopRow - 2 & ":" & "P" & iTopRow - 2)
    rFixRange.Value = Evaluate("Left(" & rFixRange.Address & ",3)")
    Set rFixRange = Nothing

    I have a worksheet with a pivot table that is functioning fine. Near the pivot filters at the top of the page, I have added a cell with list validation. It simply contains "Y, N." This drop down triggers code to remove the pivot field currently in values and replace it with another. This all works fine, but as soon as it hits Thisworkbook.Refreshall it blows the list validation out. I worked around it by just re-assigning it in the code, but does anyone know why that is happening?

    Thanks for the effort on that! I had offered a similar option (Y/N up at the top for each DOW) but there are several other similar controls lurking up there and they didn't want any more clutter. Damn you big wigs!!! :)

    No, the order of the DOW autofilter is the issue, it sounds like when using the text based DOW it cant be changed unless I sort that column, which I cant do because I need the rows to stay in date order. In the end it doesn't really matter as the filter works, I just know that as soon as the big shots pull it up, they will ignore the fact that the entire file is doing what they need and say "Why are the days of week all out of order." I am sure the will move passed it eventually :)

    The first column is dates in date order, second column is day of week. That is what I am filtering, so I can display just Friday's if I need to do some digging there. I think royUK had it, I would need to sort that underlying data in order to change the autofilter list order.

    Tried searching but I feel like I am not using the right terms. Is there a way to change the order of an Autofilter list? As pictured below the days of the week are all over the place and I am hoping I can change that without having to change the underlying data.


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tfILTER.png Views:\t0 Size:\t32.1 KB ID:\t1221766","data-align":"none","data-attachmentid":"1221766","data-size":"full","title":"fILTER.png"}[/ATTACH]

    Sorry, horrible title. I will try to clearly step though what got me to where I am.


    I have this first grid setup as pictured below. The count column is the following formula: =COUNTIF(C2:E2,"<>" & "")


    Next I add vlookup formulas in the numbered columns to pull data from an existing set. Formula is as follows: =IF(VLOOKUP($A2,$G$2:$J$13,2,0)=0,"",VLOOKUP($A2,$G$2:$J$13,2,0))


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Cap 2.PNG","data-attachmentid":1220739}[/ATTACH]
    Next I copy/paste values over all the vlookups. My issue is that all the blanks are still being counted in column B, that is unless I go into each one, click in the formula bar and hit enter.
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Cap 3.PNG","data-attachmentid":1220740}[/ATTACH]


    I know this is a really dumb convoluted process, but I am trying to repair a file broken by a coworker that is thousand of rows of data. As mentioned, pressing enter in the formula bar of a specific cell clears it, is there a way to do that to an entire range? If not, is there another way to correct those count formulas?

    Copy that, thank you!


    Out of curiosity are there any rules of thumbs around tables, like they are good up until X rows and Y columns of data, or is it pretty much machine dependent with newer versions of excel?