Search results

ah didn't see you there luke, must have snuck inbetween me looking lol

I've managed to answer my own question In pivot table - options (under pivot table name) on the data tab there is a section "retain items deleted from the source data" Number of items to retain per field - set to none

To give some background I'm bringing some data from SQL Server into my excel workbook, which I'm then using in a pivot table to display a report summarising 12 months worth of data. To do this I've setup a data connection and have an excel table updating via a view I've setup in my database...

Just to add, the reason I was asking was I saw Chandoo's post today and was wondering if it was possible to do similar, but comparing measures to different measures, rather than comparing the same measure.

Hi everyone, Have a question about calculated fields to pick your brains about. The current data source for our pivot table analysis is an OLAP cube and it appears that you cannot add in calculated fields when using this as a source. Is this the case of am I missing something? The...

I've had a play and come up with something - unfortunately I can't use dropbox at work, so I may have to try and explain what I've done here: Setup a hierarchy for your data: My example I used A1:C10 Year Make Model 2011 Audi A3 2011 Audi A5 2011 Mercedes C Class 2011 Volkswagon Golf...

Apologies, when I was trying them out I had them in columns B, C and D respectively if you put the following in B2 it should work =IFERROR(INDEX(List, MATCH(0, COUNTIF($B$1:B1, List)+(COUNTIF(List, List)=1), 0)),"") + ctrl + alt + enter

The Third formula should generate a list for that

Hi, I'm not sure 100% if I understand what your after, but should be several things you can do. To simplify make a named range called list for your A1:a46 In B2 Distinct List (A list containing no duplicates)...

Hi Nicole, For the yyyy.yy (if date is in A1) =IF(MONTH(A1)<4,YEAR(A1)-1&"."&RIGHT(YEAR(A1),2),YEAR(A1)&"."&RIGHT(YEAR(A1)+1,2)) Quarter ="Q"&LOOKUP(MONTH(A1),{0,4,7,10;4,1,2,3}) You can combine them together...

Thanks Hui, I both your methods and it still gets skipped. I checked the characters as they were passed through with debug.print and it seems Excel recognises it as a standard Capital I... T E K S T I L - D O C 0 0 0 6 6 3 9 84 69 75 83 84 73 76 45 68 79 67 48 48 48 54 54 51 57 I tried Case...

Hi, We have a process that picks up excel workbooks and then processes them into a database. The initial part of this process is to clean the filename to ensure that no illegal characters or symbols are present. The below function removes all symbols etc and also converts letters with...

I've managed to answer my own question! =CONCATENATE(HLOOKUP($CC$6,INDIRECT("'[&$A$1&]RP1'!$b7:$id1000"),$B$8-6,0),HLOOKUP($CD$6,INDIRECT("'[&$A$1&]RP1'!$b7:$id1000"),$B$8-6,0)) appears to now work with the lookup - again having the specify the whole range as it were

Hi, I've noticed some strange behaviour in the INDIRECT function between older versions of excel and 2010. I have a workbook that performs a set of looks up against another workbook. The below worked fine: =MATCH(C8,INDIRECT("'[&$A$1&]RP1'!$C:C"),0) C8 is a product code A1 is the name...

Is this not due to the SIGN function not working on a range rather rather than MATCH? If you enter =MATCH(1,SIGN($A3:$C3),0) as an array it seems to calculate ok.

Wow, I must say it didn't occur to me to anchor the range like that with COUNTIF. Very elegant solution, thanks very much.

Hi Everyone, I've been trying to highlight the first 6 non-zero cells in a range via conditional formatting. I have a test row from A1:L1 with the following values in: 0,0,0,1,2,3,4,5,0,7,8,0 The cells that should be highlighted are those with the values 1,2,3,4,5,7 I was successful in...