Recent content by polarisking

Thanks so much to all of you. The formula that works, VLETM and Monty, is elegant in its simplicity: If the row is "asking" for a Reset Amount calculation, then subtract all prior Reset Amounts from the total of all Amounts. The final formula is =IF(C2="Y",SUM($B$2:B2)-SUM($D$2:D1),"") Peter...

Please see the attached file. I have a range [Amount} defined as B2:B11. Whenever there's a "Y" in column C, Reset, I want the sum of all the Amount cells up to, and including, the row with the "Y". For each successive value of Y in Reset, I want the sum of Amount NOT INCLUDING the prior...

Assuming time expressed as 6.45 is in cell A1. =INT(A1)&" Hours and "&MOD(A1,1)*100&" Minutes left to work"

Exactly what I needed. Thank you.

As long as the first day begins in Row 8, this works =SUM(INDIRECT("B"&7+startDay&":B"&5+leadTime+startDay+1)) To make dynamic, create another named range and call it day1Begins. For your spreadsheet, the value is 8. The formula changes to...

I consider myself a fairly advance user, but I need help understanding what this formula is doing =SUMPRODUCT(--ISNUMBER(--(ROW(INDIRECT(YEAR(StartDate)&":"&YEAR(EndDate)))&"-02-29"))) For StartDate, use 10/1/2001. For EndDate, use 2/1/2014. The correct answer is 3 (2004, 2008, and 2012) and...

Your response was EXACTLY what I was looking for. Thank you.

Fixing the numerous misspellings in the original post. I'd like to run VBA against some very large datasets (> 1MM rows). Is it possible to leverage the capabilities of Power Query which can handle enormously large files and run VBA code inside of Power Query?

I'd to run VBA against some very large datasets (> 1MM rows). Is it possible to leverage the capabilities of Power Query which can handle enormously large files and run VBA code inside of Powe

Thanks for replying, Alan. So, for really large files, you can only deal with them in the PQE, Data Model, or Power BI. Would that be correct? I wonder if there’s a JET database behind all this (the engine supporting ACCESS)

I'm having trouble getting my head around the "Load" process. I get that while one's in the PQE the data file size is, for intents and purposes, unlimited. But once your transform, Data Model, and everything else is complete and your opt for Load, it seems you're back in Excel which has the...

NM. Once I upgraded to 365 everything's working the way it's supposed to.

2016

I must be doing something incredibly stupid. I have a named range with 5 pieces of data, say A1 thru A5. I want to paste this data into another sheet using the Named Range. Can't do it. Any ideas?

Here's the solution I came up with. The formulae in H13 thru H17 accomplish what I needed to do. I put A3:E25 in a named range - dataNbrs. Using the INDEX function, by column, and the Row() function to move the columns left to right, I'm able to get what I need. Hope this is helpful to some...