Formula IF Help. Want to be able to pull in data to new column but needs to look above row

[Tjillett]

Thank you in advance for whoever helps

So I have a file that a vendor is sending us that is ugly. 

I need to help in creating a formula that will do the following as seen in below.

I've already created two new columns we'll call them A & B

What I have in Excel is the following two formulas to get the data I need.

=IF(D2="",C2,B1)

=IF(B2="","",IF(LEFT(B2,2)="**","15766",IF(LEFT(B2,4)="ISLH",MID(B2,6,5),LEFT(B2,5)))) 

[GiuseppeC]

Hi @Tjillett,

can you please add a sample data spreadsheet with some desired results? It'd make it much easier to help you on this!

Thanks,

Giuseppe

[afv2688]

Hi @Tjillett 

Regarding your formulas

1)     IF IsEmpty([D2]) THEN [C2] ELSE [B1] ENDIF

2)     IF IsEmpty([B2]) THEN "" ELSE
        IF Left([B2], 2)="**" THEN "15776" ELSE
        IF Left([B2], 4)="ISLH" THEN Substring([B2],6,5)
        ELSE Left([B2], 5) ENDIF ENDIF ENDIF

cheers

[Tjillett]

We're getting there thank you, but need the table to be complete. The other rows are not being populated

[afv2688 · Accepted solution]

Hi @Tjillett ,

you have to use the multi row formula for that, and only change in the first formula [Part] for [Row-1:Part].

Since you are updating two fileds you will need to use the multi row formula twice.

cheers

[Tjillett]

Beautiful - Thank You for all of your help.

Ted Jillett

Insulet Corporation