Excel’s FILTERXML uses XPATH 1.0 which unfortunately means it is not as diverse as we would maybe want it to be. Also, Excel seems to not allow returning reworked node values and exclusively allows you to select nodes in order of appearance. However there is a fair share of functions we can still utilize. More … Read more
NOTE: I intend to make this a “one stop post” where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information. Unreliable ways of finding … Read more
If you want to get value from row in another sheet is very simple =!SHEET:ROW The WorkSheet is Data and the row I want to get value from this row A1 Example >> Function = =!Data:A1
You can use these formulas D13: =Q7 E13: =IFERROR(IF(D13+1>$S$7,””,D13+1),””) Then copy E13 to the right as far as you need.
The best for trying how function works is to try it in excel, but still… You want to know what the whole function works or just how partial functions work? In general, the function addresses to formatted table and to named parts of the table, so it should return value from formatted table, that is … Read more
In general, this is really a not a great question, as it fails the rules of StackOverflow, but still, this is some possible answer, producing this output: Public Sub TestMe() Dim myCell As Range Dim currentCell As Range: Set currentCell = Range(“D1”) Dim rangeToWrite As Range: Set rangeToWrite = Columns(“D:E”) Dim lastRow As Long: lastRow … Read more
The problem is that the numbers are being calculated to a higher precision. Try, =ABS(ROUND(K2, 2)*(ROUND(K11, 2)-ROUND(K10, 2))/ROUND(K13, 2)) Look into the ‘Precision as displayed’ option but be sure to understand all of the caveats before deciding to do it.
A solution with a formula only: We assume that A1=1-3. Fill the following formula into A2 and copy it down. =IF(A1<>””,IF(ISNUMBER(A1),IF(A1+1<=VALUE(RIGHT(A$1,LEN(A$1)-FIND(“-“,A$1))),A1+1,””),VALUE(LEFT(A$1,FIND(“-“,A$1)-1))),””) The result will be 1 2 3 This works for any numbers devided by -.
Perhaps the following VBA solution using Regular Expressions. Function FixMyPercentages(target As String) As String Dim output As String output = target With New RegExp .Global = False .MultiLine = True .IgnoreCase = False .pattern = “\s\d+\.\d+$|^\d+\.\d+\s|\s\d+\.\d+\s” Dim myMatch As Object, myMatches As Object Do While .test(output) Set myMatches = .Execute(output) For Each myMatch In myMatches … Read more
Using TEXTJOIN would be the easiest way, but if you wanted you can also use a VBA code. Function Concat(rng As Range, Optional sep As String = “https://stackoverflow.com/”) As String Dim rngCell As Range Dim strResult As String For Each rngCell In rng If rngCell.Value <> “” Then strResult = strResult & sep & rngCell.Value … Read more