Formula help

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007
Hi, :)


I'm looking for help with the Below formula.

(IF(AND(K3>=D$41,K3<=E$41), AZ$2,"") Formula has to be dragged all the way to the right hand side as far as right goes, and down about 50000 cells. For the time being I had to do each cell manually, including visual error checking and it's like 🤣

Thanks in advance and I appreciate the help :)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Highlight the area that is going to contain the formulas, starting at the top left cell
Enter the formula and then press CTRL + Enter
 

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007
hi thanks for responding. oops! i forgot to state that the formula for each cell would change accordingly, when dragged across and then down
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Yep, it does....but this may be quicker, bur change the ranges in the first aprt of the code to suit
VBA Code:
Sub MM1()
Range("A1:I50000").Formula = "=IF(AND(K3>=D$41,K3<=E$41),AZ$2,"""")"
End Sub
 

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007

ADVERTISEMENT

so it would be
(IF(AND(K3>=$D$41,K3<=$E$41), AZ$2,"")
(IF(AND(K3>=$D$42,K3<=$E$42), ba$2,"")
 

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007
hi, i have another sheet with the macro but it kept on crashing the sheet so i had to delete it to create and use the formula instead.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

How many columns / rows do you want to apply the formula to ?
The macro shouldn't crash unless you are going ALL the way across to the last column, because "AZ$2" dragged across would be off the end of the worksheet!!
The formula method will adjust for ANY range that is allowed to change....
=IF(AND(K3>=$D$41,K3<=$E$41), AZ$2,"")
So, if this has 2 absolutes($signs) it won't change
The AZ$2 will chnage the columns as required bot not the rows.
Does that make sense
 

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007
yes that makes sense, the AZ2 is not the problem it's the middle of the formula that needs to be modified, meaning D and E column references should not change when dragged across, but would change only when dragged downwards(row numbers would change accordingly), so then when dragged downwards D41 and E41 would become D42 and E42.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, did you modify the required range in the code, then give it a try....that's what it does
Why not test it on a small range
VBA Code:
Sub MM1()
Range("A1:D50").Formula = "=IF(AND(K3>=$D41,K3<=$E41),AZ$2,"""")" 'change ranges to suit
End Sub
 

deuce

Active Member
Joined
Oct 6, 2006
Messages
346
Office Version
  1. 2007
na, no macros, they cause too many problems. I have 15 separate workbooks, none of the macros in them work. However, the ones with formulas however many they are they work flawlessly though.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,116
Messages
5,768,211
Members
425,459
Latest member
Danniey

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