Search results

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...

@Chandoo: I've read the post, and found nothing that I can use. @JP: thanks for the code !. IT works the same, and still cannot debug line-by-line the code when error. And the "error" sentence does not appear. For instance, I have "Client1" as a value in a cell, and when applying the UDF...

hi All, I've written this User Defined Formula, to extract a string from within another, delimited by underscores, e.g. AB_CDE-1234-56-7_89_FGH, and the result is CDE-1234-56-7. here's the code (it's in Spanish, but it makes no difference) Function extraecontrato(celda As range) Dim...

All, I have a workbook with a sheet per office, with the same table each time. i need to send to each office a new workbook with its specific data sheet, plus a pivot table on another sheet, out of that data. Also, I need to create a data entry table, with the same layout as the pivotTable...

well, something had happen, I can now see my good ol' Coyote !!!

let's see if it works... nope, still have the same avatar, not the one I've created at gravatar... :(

All, After reading a post on contextures.com, I'm determined to use a geographical map to show some data for the region, e.g: budget, forecast, actual. My first idea was to show within each shape the name of the shape and the value I wanted to display. I've tried using...

Hi there !! My name is Martin, I'm from Buenos Aires, Argentina. Used to be IT Manager, now promoted to Regional Administration. Love to use Excel, pretty much for everything (I've even created and distributed personalized HTML files from a user list in Excel, using Lotus Notes !!) Since...