0

Look in the first Image, Row 10 having value 0.096 now see the formula bar =+IF(ISNUMBER(SEARCH("Stirupps",B106,2)),IF(C106=8,0.075,(C106*8)/1000),IF(ISNUMBER(SEARCH("Link",B106,1)),(C106*10)/1000,))

enter image description here

Let's start with

IF(ISNUMBER(SEARCH("Stirupps",B106,2))

Search

("Stirupps",in cell B106 , Start pos)

This will return True

now ISNUMBER Func will convert it to 1

IF(ISNUMBER(SEARCH("Stirupps",B106,2)) Logical Expression

True

IF(C106=8,0.075,(C106*8)/1000)

False

IF(ISNUMBER(SEARCH("Link",B106,1)),(C106*10)/1000,))

Again Search

Search("Link",in cell B106, Start pos)

If True 

(C106*10)/1000,)

If False 

`))`

Now when I paste this formula in cell H16, look the Second Image

enter image description here

It will search the Strings in Cell B16 which doesn't match. It will return nothing

Now my question

How can it return the earlier value, when the cell does not contain the string "Stirrups" and "Link".

I want the value to remain unaffected in cell H16.

Dour High Arch
  • 21,513
  • 29
  • 75
  • 90
Jatin Sharma
  • 13
  • 4
  • You don't have entered `ELSE`-cases for thesecond and third `IF`. Which value do you want to retain? – OverflowStacker Jun 17 '20 at 11:46
  • There is not directly way in IF condition but there is a way with coding VBA in `Worksheet_SelectionChange` event. look this post: https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba – Sharif Lotfi Jun 17 '20 at 11:48
  • @ OverflowStacker The value in second image Cell H16 – Jatin Sharma Jun 17 '20 at 13:20

0 Answers0