Search results

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

Thanks to all for your responses. I'm sorry it took me so long to get back to you.

I've included a file. Col A and Col B are sets of numbers. Col C denotes which rows to sum using SUMIF. The first formula (cell F3) is =SUMIF($C$3:$C$25,"X",$A$3:$A$25) I want to be able to drag/copy this formula down and have the third argument point to $B$3:$B$25. Using a drag or copy...

I posted this a couple of days ago in the Excel area, and got a couple of Power Pivot responses. My dilemma is that I'm bringing in the EU Date with time, and it's not converting to US. In those cases where there's no time component, the conversion works just fine. Here's what I use using the...

Thanks to both of you for the Power Query solution. Right now, I'm processing the file using VBA: >>> use code - tags <<< With wsExtract.QueryTables.Add(Connection:="TEXT;" & strFileToOpen, Destination:=wsExtract.Range("A1")) .FieldNames = True .TextFileParseType = xlDelimited...

I'm having difficulty converting EU dates (DMY) that include a time component into US (MDY). See the last 2 rows (36 and 37) of the attached file. I want them to resolve to 06/25/2021, but they're staying at 25/06/2021. I've tried a number of techniques (Import wizard), Text to Columns, etc...

Marc L, I think you may have helped me in the past. I'm not sure how to interpret your response.

This is helpful. I was hoping there'd be a single command driven statemente (like ERASE) that would do the same thing. Certainly the advantage of your suggestion is using the Upper Bounds values.

When I use ERASE in the forms of: Erase arrTest, or Erase arrTest() the entire array structure is obliterated: the dimensions are gone, it looks just like another variable. Right now, I'm looping through the dimensions and setting the values to whatever fits (0, "", etc.) Any ideas? Thanks...

Very helpful. What does the Resize do?

Say I want to do something with columns 2 through 5. Is there a way to specify Columns(2:5) or something similar? What about a set of non-contiguous groups like 2-5, 7, 8-11, and 15? (can this be done with letters?) Thanks in advance.