Recent content by droopy

Hi Belleke, I drafted a solution that appears to be quite similar to Bosco's (well I mutliplied by 1000 rather than 0.5, but otherwise it is very close), except that I made a table in your "Reservaties" sheet, because it makes the formula more readable, and it is more flexible, as the table...

In the sub-formula below: Invoiced will be added if none of them is 0 => (Table1[INVOICED]*(Table1[NET]<>0)*(Table1[REV AMT]<>0)) the blue amount will be summed only when the purple amount is not 0 (or empty), and when the green amount is not 0 or empty. This is because the (Table1[NET]<>0)...

Well, I would have said "yes, using data > text to column, and choosing space and S as delimiters". However, it seems that you have several cells which contain newline characters (\n\r aka crlf aka chr(13)chr(10)). Except if someone knows a way to remove them with search and replace, I would go...

See attached example. Sumproduct, but with no array formula (and no added column). I changed your Sheet2 range to Table for flexibility. Be careful that in your sheet2, sometimes there is a net and a invoiced, but no rev amt. In this case nothing is added, as I exactly applied your request...

Not sure if I properly get what you are trying to achieve, but if you want to replace all "Free Standing" strings with "MySheets" strings in your formula, I would select all the cells where this formula appears, and CTRL+H (kb shortcut for "search and replace"), then type Free Standing in the...

Probably the best solution (unless someone else finds a flaw): No array formula, no delimiter, no cell type dependency.

There are pros and cons to each solution: Index and Match solution is more flexible, because it will work regardless of the type of data in column C. Sumifs will only work if column C has numeric values, but it does not need the CSE (ctrl+shift+enter) to make it an array formula, which is more...

Hi Chihiro, Thank you for your response. FY18Q4 should not be filtered out. That's just a mistake. And the reason why I sum backward is because I have ongoing deals as of today. They have an expected completion date in the future. As I am in FY17 Q2, I want to have the volume of what is ahead...

Hi Chihiro, I receive the data this way (with more columns indeed), except the calculated columns (margin %), and the fiscal quarter which is actually a lookup on a dimdate table where the mapping is done, based on an end date in the raw data. Anyway, let's assume the raw data are only: ID, A...

I would take PCosta formula and change it to: =INDEX($C$2:$C$89,MATCH(F2&"-"&G2,$A$2:$A$89&"-"&$B$2:$B$89,0)), and still apply it as array formula (Ctrl-Shift-Enter). Reason is because if you have, for example, 3 in VT and 21 in CT , and elsewhere 32 in VT and 1 in CT, both will match PCosta's...

Well, my browser was was causing the issue with the file type. I took another browser, and now it's ok. Please see attached.

Hello, I am trying to show forecasts with cumulated data. My data table is as follows: ID - unique identifier A Revenue - number or "-" if empty A Margin - number or "-" if empty A Margin% - formula: =iferror([A Margin]/[A Revenue],"-") B Revenue - number or "-" if empty B...

I noted in your destination worksheet, that you have some formulas in column L. Please find below some changes to cope with that too. First of all, I recommend that you delete the extra rows on worksheet "both". We'll take care of them in the listrow object in vba code. Although in this...

From file extension I guess your XL version >= 2007. If yes, how about making your "both" data sheet a table (through insert > table). The code below assumes your range selection macro works (I did not give it a try), and both source (Book1.xlsx) and destination (data.xlsm) workbooks are opened...

Hi Peter, Sorry for the late answer, I got preempted on something (more) urgent. Thank you for your response. While your solution also satisfies my need, I finally managed to solve the issue by adding a (boolean) calculated column in Powerpivot, which is easier to maintain than VBA code. I...