# Posts by pdrobinson

• ## SumIfs - Define the Sum_Range by referencing a cell value

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?

• ## SumIfs - Define the Sum_Range by referencing a cell value

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])

• ## Add Unique ID to new Row, which is +1 of the current Max ID, using Macro

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

• ## Add Unique ID to new Row, which is +1 of the current Max ID, using Macro

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

• ## Help needed replacing parts of numbers with other numbers

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

• ## Data Validation

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?

• ## Add Unique ID to new Row, which is +1 of the current Max ID, using Macro

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

• ## Add Unique ID to new Row, which is +1 of the current Max ID, using Macro

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.