Replace Values in Cell

[bnageshrao]

I have a spreadsheet and two of the columns are called Lead Name and AM Name

Lead Name AM Manager
1. InValid Invalid
2. Invalid (Blank)
3. Rick Hu Thomas King
4. (Blank) John Reid

I have to update the Lead Name Column and the criteria is if the Lead Name has a value of Invalid or if it is Blank, then I have to replace those values with the first AM Manager name value. If the first AM manager name is either invaild or Blank, then I have to choose the next AM manager's value. So what I want to achieve in those columns after the update is as follows.

Lead Name AM Manager
1. Thomas King Invalid
2. Thomas King (Blank)
3. Rick Hu Thomas King
4. Thomas King John Reid

Is it possible to use any function using formulas to achiev the same?, if a sample code is shown then it will be useful, thanks in advance.

 

[SkipVought]

Hi,

Why wouldn't the row 4 Lead Name be John Reid using your logic?


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[bnageshrao]

That is because I have to get the first AM Manager value from the beginning as soon as I find blank or null value in lead name column.So it will be Thomas King. In fact basically I will be finding the first value in AM manager column and then whenever i find blank or invaild in lead name column, i will be replacing those with the same value. Thanks