Struggling to get multiple arguments to work in a formula

shina67

Board Regular
Joined
Sep 18, 2014
Messages
138
Hi All,

I have attached a link to the spreadsheet I am referring to:- https://www.dropbox.com/s/i5lvozes381hj18/Test Scan Mr.Excel.xlsm?dl=0

If the part from A1 "XPGLIDE" matches F3 then I want N3 to populate the same as A3, but the rest of K3 through to Z3 to return Zero.
Similar if A1 was changed to "25412AYERSROOF." and F4 matches the part "ROOF" then I want to populate X4 the same as A4 but the rest of K3 through to Z3 to return Zero.
As you can see from the spreadsheet there are 16 different variations (see row 1) that could possibly be in column F and hence could be in A1.
The formula in N3 is something I thought would work but unfortunately is not.
If there is a match I then need the same cell to populate the difference between cells AF and AI.

I hope this makes sense when you look at the spreadsheet.

I have googled and tried different various ways but I am at a dead end.
I hope one of you helpful people can help resolve this for me.

Thanks in advance.
 

Excel Facts

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

A couple of remarks regarding consistency between cells A1, F3 and N1 ...

A1 : 25412AYERSXPGLIDE
F3 : XPGLIDE
N1 : XPGLIDE

In cell N3 ... you can test following formula :

Code:
=IF(AND($F3=N$1,ISNUMBER(FIND($F3,$A$1))),$A3,"")

Hope this will help
 
Upvote 0
Thanks James for your reply.

Your solution returns a zero for all. What I am wanting is for it to also return the value from $A3 if $F3=N$1


Hi,

A couple of remarks regarding consistency between cells A1, F3 and N1 ...

A1 : 25412AYERSXPGLIDE
F3 : XPGLIDE
N1 : XPGLIDE

In cell N3 ... you can test following formula :

Code:
=IF(AND($F3=N$1,ISNUMBER(FIND($F3,$A$1))),$A3,"")

Hope this will help
 
Upvote 0
Hello,

The proposed formula returns 6 ... in cell N3 ... :)
 
Upvote 0
Hi James,

I am using the following formula which seems to work fine :-
=IF(AND($F3=SUBSTITUTE(N$1," ",""),$D3&$E3&$F3=$A$1),$A3,"")
The problem I now have is that when $A$1 is blank it does not remain at 6.
 
Upvote 0
Thanks all for your help so far.
I now have a new problem.

I’m hoping you can help with a drag down of formula’s.
Row 3 works fine for what I need it to do. However when I drag the formula’s down and then insert the source data into row 4 column G through to column Z there becomes a problem and downwards.
If in A1 I input 25412AYERSXPGLIDE.. and then a quantity into A2, row 3 updates as it should.
Then if I input 25944ELLISXPVIEW. into A1 and a quantity into A2 row 4 won’t update as row 3 did. Also cells AK3 and AL3 go wrong.
Any help on this will be appreciated.
I have attached a link to the workbook.
https://www.dropbox.com/s/d3ya2v7ls4...Scan.xlsm?dl=0
 
Upvote 0
Hi,

Sorry but the file you have added a link to ... does not hold the formula your are referring to ...???
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top