MAXIF formula Changes with insertion of Row

[Livin404]

Good afternoon, thanks to advice from this site I got pointed in the right direction. I am using a MAXIF formula to continually get the last date and time a piece of equipment was issued or returned. I have a Macro when pressed it will send details to “Signup” and doing so it inserts a row because the latest updates need to be in the top rows. I have entered the following: {=MAX(IF('Sign out '!$C$4:$C$3000='Tool Data'!B3,'Sign out '!$E$4:$E$3000))} and it works perfect. However, as soon as I use the Macro again it changes my MAX if formula like seen here: {=MAX(IF('Sign out '!$C$5:$C$3001='Tool Data'!B3,'Sign out '!$E$5:$E$3001))}. I tried tinkering around with the $$ better that doesn’t seem to matter. I’ve included the Macro. The Macro seems to be working as designed. Any suggestions or help would be appreciated.

Sub SIGNOUT1()

'

' SIGNOUT1 Macro

'

Range("C28:G28").Copy

With Sheets("Sign out ")

.Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False

.Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End With

End Sub

Sub SIGNIN1()

'

' SIGNIN1 Macro

'

Range("C28:G28").Copy

With Sheets("Sign in ")

.Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Application.CutCopyMode = False

.Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End With

End Sub

 

[jasonb75]

If you insert rows above a formula range then the formula range will move down, if you insert rows inside the formula range then it will expand. Deleting rows will have the reverse effect.

There are many workarounds, perhaps the easiest would be to use MAXIFS (if your version of excel has it) which, if used with entire column references would possibly still be more efficient than your array formula. Failing that, one alternative would be

=MAX(IF(INDEX('Sign out '!$C:$C$,4):INDEX('Sign out '!$C:$C$,3000)='Tool Data'!B3,INDEX('Sign out '!$E:$E$,4):INDEX('Sign out '!$E:$E$,3000)))

 

[Livin404]

Hello thank you for getting back to me: I do know my work computer has more restrictions, so I tried the formula you provided, and I it gives the standard error type thing, let me know to use an apostrophe if needed. It did shade in the fist $C$ part of the formula. I think a few extra bits of the formula ended up twice though I may be wrong. I apologize profusely if I am.
Thank you very much indeed.

 

[jasonb75]

Sorry, the extra $ was a typo (actually 4 typos where I used copy and paste for the rest of the formula). Correct formula below.

=MAX(IF(INDEX('Sign out '!$C:$C,4):INDEX('Sign out '!$C:$C,3000)='Tool Data'!B3,INDEX('Sign out '!$E:$E,4):INDEX('Sign out '!$E:$E,3000)))

If it should not be possible for the criteria ('Tool Data'!B3) to be found in C1:C3 or anywhere in column C below C3000 then you might be able to use this instead.

=MAXIFS('Sign out '!$E:$E,'Sign out '!$C:$C,'Tool Data'!B3)

If the formula shows an error of #NAME? then it means that your version of excel (2016 or older) does not contain that function so you would need to stick with the first formula.

 

[Livin404]

Hello, thanks for getting back to me with both those options. The good news is I got no error messages, the bad news, nothing happens the date is not placed in on the tool status. I tried both of them and got the same result.

 

[Livin404]

There was nothing wrong at all with your formula. I made one mistake not related to your formula. My apologies. It was perfect!! Thank you so much.