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))))

Input

Tips and Tricks

Accepted answers

afv2688

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

All comments

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

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

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703