If then formula

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
I wonder if you can help on this please

This is what i am trying to do

IF B2=C7 THEN B1=B2, IF C2=C7 THEN C1=C2, IF D2=C7 THEN D1=D2, IF E2=C7 THEN E1=E2

This follows the same pattern up to M2=C7 THEN M1=M2

Any help on this would be great please
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,791
Office Version
  1. 365
Platform
  1. Windows
In B1 enter the formula

=IF(B2=C$7,B2,"")

Then drag it down to M2 with the fill handle.
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
Hi JasonB

I can't use this formula because i have a piece of code that will populate cells in column "B" if cell "B1" have anything in it which includes a formula
This is why I want to only populate the cell if it meets the criteria
 

BeBoozer

New Member
Joined
Aug 29, 2013
Messages
1
Will this work:

IF (B2=C7,B1=B2,IF(C2=C7,C1=C2,IF(D2=C7,D1=D2,IF(E2=C7,E1=E2))))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,791
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case you will need to do it with more code, maybe a worksheet change or calculate event, depending on whether or not the cell to be monitored for change contains a formula.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Hi there,

Jason almost had it. Need to lock the column, not the row. Enter this in B1 and copy to E1:

Code:
=IF(B2=$C7,B2)

HTH
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
Zack

This is my problem, the code below populates row four in the column that has data in row 1
If i have a formula in the cells in row 1 then this affects row 4
I need only the answer in row 1 if it meets the correct criteria and only one column in row 1 will meet the criteria

LC = Cells(1, 1).End(xlToRight).Column




If LC = Cells.Columns.Count Then Exit Sub
With Cells(4, LC)
.FormulaR1C1 = "=('Total Inventory'!R[37]C+'Total Inventory'!R[42]C)/2"
'.Value = .Value
End With
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
I was trying to solve it this way with code

With Range("B1")
.FormulaR1C1 = "=IF(R[1]C=Update!R[6]C[1],'Stock Turn Ratio'!R[1]C,"""")"
.PasteSpecial Paste:=xlPasteValues
End With

So i would just paste the answer but i can't get this to work either
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
It's very unclear as to how your worksheet is structured. Could you give a detailed description of what your data is, where it's at, and specifics on the expected outputs?
 
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,168,025
Messages
5,856,909
Members
431,837
Latest member
megantang

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