MAXIF formula Changes with insertion of Row

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
Sorry, the extra $ was a typo (actually 4 typos where I used copy and paste for the rest of the formula). Correct formula below.
Excel Formula:
=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.
Excel Formula:
=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

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,966
Messages
5,621,874
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top