Recent content by Martin

Luke, I thought on the same, and then saw your comment... the problem with that is the transposition of one of the arrays. If you Evaluate the formula, you'll see it clearly. that said, The fastest solution I can think of is a =SUMIF(A2:A5;1;B2:B5), for each week and cat. nevertheless...

SMC001, can you show an example of the first 5 lines of your file, including title, separated by the | (pipe) symbol? Maybe I can play around a little bit and get back to you with some ideas... thanks !!!

WOW !!!!!! now, you are only one step closer to the top !!! I'm going to review it, as the results seem to be the same, and see if I can get rid of all the IFs, using Daniel Ferry's technique of treat IF as boolean operations. in any case, my inventory is now even faster !!! Thanks again...

Luke, you are a Monster!!! (in the best sense of it !!!) Now, we all know index/match is better than offset as it is non-volatile, hence faster. Your mission, should you choose to accept it, is to upgrade your current formula, avoiding the use of vlookup !!! That said, you are now my...

sure !! when adding a new record, the formula is changing cell references incorrectly. this is for row 498, the last on my list: =INDEX($C$2:$C497;MAX(IF(($A$2:A497=A498);ROW($A$2:A497)-1))) this appears for row 499, the new record...

BTW, I am using that formula in a List. I've noticed that whenever I enter a new row, that formula copies itself wrongly. Any comments? Thanks !

Luke, I Believe I found it !! =INDEX($C$2:$C481;MAX(IF((A$2:A481=A482);ROW(A$2:A481)-1))) entered as an array. any non-array ideas? Thanks !

for the first value, yes. But not for the rest. It returns a #Value! Error.

THanks, Luke ! Yes it is. but not necessarily by product

Hi forum ! it's been a while since last visiting.... Yesterday I found myself in front of a new Excel challenge, which I'd like your feedback on. I have a list, with 3 columns: product description, last purchase date, Qty. I wanted to display in a next column the last qty purchased for each...

Well, it seems I've found the problem. the name of the field I was trying to add a calculated item to was "Year". I just changed it to "Period", and it worked like a charm, allowing me to add the delta between years, or "periods" now. hope this helps to anyone else. Rgds, Martin

Hui, thanks for your reply. Sorry I did not get back to you earlier: I did read your post, but got stucked with work... Now I am trying to do what you said. I have even created the PT from scratch, and the problem remains....

All, I have this pivot table with year as a Page, Average of amount as Data, country as Row, and Type as Column. Type has 2 concepts: C1 and C2. The result is that I am showing averages for C1 and C2 per country. I need to add the Ratio C1/C2 as a concept. I tried as a calculated item, but...

Hi all, I am using Excel 2010 now, and I am concerned about Conditional Formatting. when you select any icon set (colored arrows, for instance),icons appear aligned to the left of the number, instead of the right, so the corresponding icon is far from the number, and the next icon is closer...

@Hui: Thanks, worked as a charm !!! I've done a small addition after the calc of FinCadena, as I still got a #VALUE error, for those cells where there was only a single "_": If FinCadena = 0 Then FinCadena = Len(celda.Value) In that way, I always get a positive length of the string. @JP...