Posts by pdrobinson

    Hi, thanks


    Nearly there. I tried this and it seems to be failing at the end of the Indirect calculation. I had to add in an extra Oct[Dates] as the rest of the sumif references broke.


    =SUMIFS(INDIRECT("Oct["&[@Server]&"]),Oct[Dates]"),Oct[Dates],">="&[From],Oct[Dates],"<="&[To])


    attached Capture 1 shows it is finding the reference but then it fails to #REF on next step (capture 2)


    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210531}[/ATTACH]


    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210532}[/ATTACH]


    any thoughts?

    Images

    • Capture1.PNG
    • Capture2.PNG

    Hi,


    I have a data dump which has Servers (AMEWEW1ECCS01 being one) as headers and dates in column A. Then values are populated throughout the table for each date in each server.


    This Table is named Oct and each column name is the server name, as in Oct[AMWEW1ECCS01]


    I need to sum all values in the Oct table that are between a date range and for a specific server. This date range is defined by the [To] & [From] columns in another Table. The below works perfectly if I manually tell it which server it is.


    =SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])


    Now I need to make the formula look at a cell in a column called [Server] and populate the Sum_Range accordingly. So replacing the red bit below to reference whatever is in the [Server] column. Any thoughts


    =SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

    Thank you for your help. Thought I would close this as I finally have it working.


    Hi, thanks very much. I think I inserted it correctly below. I have reset the A column to be Number format for now. Unfortunately when I run it I get the new row, but followed by an error alert saying Overflow.


    (Also, how does everyone paste their code in such a presentable manner, when I paste below it looks really messy lol)



    Sub InsertARow()
    Dim r As Long
    On Error GoTo ErrHandler
    ' Get the row number
    r = ActiveCell.Row
    ' Unprotect the sheet
    ActiveSheet.Unprotect Password:="123"
    ' Insert a row
    Range("N" & r).EntireRow.Insert
    ' Fill down the formula from above the inserted row to below it
    Range("N" & (r - 1) & ":N" & (r + 1)).FillDown

    'Insert ID - Assuming column A is number and not text
    Dim MaxID As Integer
    MaxID = Application.WorksheetFunction.Max(Range("A:A"))
    ActiveCell.NumberFormat = "000"
    ActiveCell = MaxID + 1

    ExitHandler:
    ' Protect the sheet again
    ActiveSheet.Protect Password:="123"
    ' Get out
    Exit Sub


    ErrHandler:
    ' Report the error to the user
    MsgBox Err.Description, vbExclamation
    ' Always go past the exit handler section
    Resume ExitHandler
    End Sub

    I think you could do this in an IF formula, using a mixture of LEFT, RIGHT and CONCATENATE.


    For example, the below will replace the first three digits for you. You would have to create something similar to replace the other digits using MID instead of LEFT


    =IF(LEFT($A1,3)="004",CONCATENATE("056",RIGHT($A1,8)),IF(LEFT($A1,3)="012",CONCATENATE("048",RIGHT($A1,8)),IF(LEFT($A1,3)="020",CONCATENATE("052",RIGHT($A1,8)),IF(LEFT($A1,3)="024",CONCATENATE("044",RIGHT($A1,8)),IF(LEFT($A1,3)="028",CONCATENATE("040",RIGHT($A1,8)),"")))))


    Someone better than I will likely have a cleaner answer, and probably get both in the same formula. I am by no means fluent in this but hopefully it helps a bit

    Hi, I just cut and pasted your formula into a clean excel sheet and it worked perfectly. It prevented all other letters and prevented in lower case. Have you tried in a clean sheet to rule out anything else conflicting with this?

    Here it the current code.


    Sub InsertARow()
    Dim r As Long
    On Error GoTo ErrHandler
    ' Get the row number
    r = ActiveCell.Row
    ' Unprotect the sheet
    ActiveSheet.Unprotect Password:="123"
    ' Insert a row
    Range("N" & r).EntireRow.Insert
    ' Fill down the formula from above the inserted row to below it
    Range("N" & (r - 1) & ":N" & (r + 1)).FillDown

    ExitHandler:
    ' Protect the sheet again
    ActiveSheet.Protect Password:="123"
    ' Get out
    Exit Sub


    ErrHandler:
    ' Report the error to the user
    MsgBox Err.Description, vbExclamation
    ' Always go past the exit handler section
    Resume ExitHandler
    End Sub

    Hi,


    I have a spreadsheet with a Macro which identifies the Active Cell and inserts a new Row in that location. Column A of the Table is for the ID number.


    I want the same Macro to self populate an ID in Column A of the new Row. I need it to be +1 higher than the maximum ID number already present in that column. In case it matters, Column A is using a Special Format (000) to ensure it always contains three digits. For example the number 3 will be shown as 003. Not sure if this matters.


    As Rows will not always be at the bottom of the Table I cannot do a simple increment of the line above.


    And this is where i am stuck as I am not very good at VBA. Any help would be appreciated.