Search:

Type: Posts; User: Ron Coderre; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.10 seconds.

  1. Replies
    2
    Views
    151

    Re: Formatting a number

    Assuming the actual text value is: '@1392293

    You could try this custom number format which applies your desired format to the cell value if it is text:

    0;0;0;'@'
    With that format, the above...
  2. Replies
    5
    Views
    176

    Re: Label Last Saturday

    With these dates in A1:A5

    Tuesday, January 01, 2019
    Friday, February 01, 2019
    Friday, March 01, 2019
    Monday, April 01, 2019
    Wednesday, May 01, 2019

    This regular formula, copied down,...
  3. Replies
    8
    Views
    342

    Re: Set borders dynamically with vba

    I'm not against VBA...but there may not be a need for it in this case.

    Using this sample data in A1:F10

    Alpha Bravo Charlie Delta Echo Foxtrot
    B E E ...
  4. Replies
    3
    Views
    151

    Re: Detect if Reference Workbook is Open

    You might be able to use something like this:

    If a workbook is linked...AND...is currently open
    the linking formula has this structure:

    D2: ='[FileName.xlsx]Sheet1'!A1

    BUT...if the file is...
  5. Replies
    2
    Views
    161

    Re: Running more than 1 excel

    • Open the first instance of Excel
    • While Holding down [ALT]
    - Right-Click the Excel icon
    - Select Excel
    - A prompt will display asking if you want
    to open a new instance

    Does that help?
  6. Replies
    2
    Views
    146

    Re: return numbers from a string

    With your sample data in A1:A10


    These formulas, copied down, return the values you're looking for:

    B1: =TRIM(LEFT(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),LEN($A1)*3))
    C1:...
  7. Re: Power Query - leaving detailed notes of steps

    I open the Advanced Editor and put my comments there.

    // for single line comments

    /* for multi
    line comments */

    Is that something you can work with?
  8. Re: Sum text that contains numbers - But ONLY the numbers.

    I restructured your data a bit



    Using this:
    A B C D E F G H I J K L
    1 ...
  9. Replies
    3
    Views
    151

    Re: Number formatting

    I had a similar situation a few years ago.
    Here's what I did:
    • Created a Custom Cell Style that only featured a number format.
    • Assigned that cell style to all impacted cells
    • Used VBA to...
  10. Replies
    3
    Views
    806

    Re: Microsoft Query > Power BI

    Seamless....Build your Power Query solution...Check the: Load to Data Model box. Use Power BI. Done
  11. Replies
    3
    Views
    806

    Re: Microsoft Query > Power BI

    MS Query is dead as disco.
    If your source data is not normalized....Transform your data with Power Query (instead of MS Query). It's fairly easy to learn and we're here to help.
  12. Replies
    4
    Views
    183

    Re: update to 64 bit

    Here's an example of where the PtrSafe keyword gets inserted for API calls:

    Public Declare PtrSafe Function SHGetSpecialFolderLocation Lib "shell32.dll" _
    (ByVal hwndOwner As Long, ByVal nFolder...
  13. Re: Power Query: Using a Text Parameter to Return a [FieldName] List

    Maybe something like this?

    let
    Year1Ref = "FY19",
    Year2Ref = "FY20",
    Source = Table.FromRecords(
    {
    [ProjID= "P1", Yr1 = 1, Yr2 = 1],
    [ProjID= "P1", Yr1 =...
  14. Re: Can i Turn off Excel Table Auto Fill formula (Formula Replication) for only specific column(s)?

    No difference between using grouping and hiding. I just hide the row so my esteemed colleagues won't accidentally write over the "skip" formulas.
  15. Re: Can i Turn off Excel Table Auto Fill formula (Formula Replication) for only specific column(s)?

    This is what I've done in those circumstances....

    • Insert a row at the top of the table that contains ="SKIP" in every column.
    - Use UNDO if necessary to remove replication of that formula.
    -...
  16. Re: Subtracting from previous cell in column with data

    Try this formula:

    D6: =IF(C6="","",IFERROR(C6-LOOKUP(1E+99,$C$5:C5),N(C5)))
    Does that help?
  17. Replies
    4
    Views
    739

    Re: Power Query Nest If Statement

    Using this data in an Excel Table named Table1:

    MthName
    Jan
    Feb
    Mar
    Apr
    May
    Jun
    Jul
  18. Replies
    3
    Views
    239

    Re: Power Query Group by and Sum

    I put your sample data in an Excel Table named Table1 and connected it to Power Query.
    In the query editor:
    - Select the first 3 columns...Transform.Unpivot.Unpivot_Other_Columns
    - Select the...
  19. Re: Define end of range based on another cell value (offset?)

    To avoid volatile functions, try something like this:

    =AVERAGE(F37:INDEX(F:F,37+B16-1))
    Is that something you can work with?
  20. Re: Pivot table - variance of item counts year to year

    If you have a later version of Excel.....You can drop the Count field into the values section a second time and select Display As: Difference from Previous.
    Experiment to get the settings correct.
    ...
  21. Re: Return the values between text characters

    Hmm...didn't even consider that the text might have characters before the #.
    With
    A1: abc #1 5 of 214 H

    These regular formulas return the first and second values

    B1:...
  22. Re: Return the values between text characters

    As long as the pattern is constant...
    With a test string in A1....eg #15 of 214 H

    These regular formulas return the first and second numbers:

    B1: =MID(A1,2,SEARCH(" of",A1)-1)
    C1:...
  23. Replies
    3
    Views
    239

    Re: Power Query Group by and Sum

    Can you post some sample data and the results you want to see, based on that sample data?
  24. Re: Loading a file using power query that contains a date

    Try this:
    - Enter the file path in a cell
    example: C:\Documents\my_excel_file.xlsm
    - Name that cell FilePath
    - Select that cell and....Power_Query.From_Table/Range

    That will open the Power...
  25. Replies
    4
    Views
    211

    Re: text formatting

    With
    A1: $7,564,264.00

    This regular formula returns the string you're looking for:

    B1:=TEXT(--SUBSTITUTE(TEXT(A1,"0.00"),".",""),"0000000000000000")
    This also seems to work:

    B1:...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4