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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,900
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
11,900
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

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