Hello,
I'm wondering if anyone can help me? I'm trying to use a combination of an If equation and a VLookup across two different spreadsheets and I'm not sure how to word the formula.

Basically, on the SZX TEST 1.0 spreadsheet, if the contents of Column C is 'Y' then on the Column B on the SZX Delivery Note 1.0 is filled in with the VLookup, and if Column C is blank, then Column B is also blank.

The formula I've been trying is:
=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZEN TEST (2).xls]MT'!$B:$AA,6,FALSE)","")

And I know the VLookUp part is right but I think I've messed up on wording of it perhaps? To be honest Column C doesn't need to be 'Y', it could be a number like '1' just as long as the formula works I'm not fussed on letter/number is in Column C.

I realise I probably haven't explained it too well, but I've attached the two spreadsheets if that helps?

Any help would be greatly appreciated!!

Many thanks,
Liz

Dear LiziC, Good Afternoon.

Try to change your formula.

Before=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZEN TEST (2).xls]MT'!$B:$AA,6,FALSE)","")

Now..:=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y",VLOOKUP($A:$A,'[SZX SHENZEN TEST (2).xls]MT'!$B:$AA,6,FALSE),"")

Please, tell me if it worked for you.

Thank you!!! It worked!
You've just saved me hours and hours of staring at the screen trying different things!!

<<<<< HELP from BRAZIL

I´m happy that it worked.

Have a nice day.

On Wednesday, May 2, 2012 1:55:27 PM UTC-5, LiziC wrote:
Hello,
I'm wondering if anyone can help me? I'm trying to use a combination of
an If equation and a VLookup across two different spreadsheets and I'm
not sure how to word the formula.

Basically, on the SZX TEST 1.0 spreadsheet, if the contents of Column C
is 'Y' then on the Column B on the SZX Delivery Note 1.0 is filled in
with the VLookup, and if Column C is blank, then Column B is also
blank.

The formula I've been trying is:
=IF('[SZX TEST 1.0.xls]MT'!$C:$C="Y","=VLOOKUP($A:$A,'[SZX SHENZEN TEST
(2).xls]MT'!$B:$AA,6,FALSE)","")

And I know the VLookUp part is right but I think I've messed up on
wording of it perhaps? To be honest Column C doesn't need to be 'Y', it
could be a number like '1' just as long as the formula works I'm not
fussed on letter/number is in Column C.

I realise I probably haven't explained it too well, but I've attached
the two spreadsheets if that helps?

Any help would be greatly appreciated!!

Many thanks,
Liz


+-------------------------------------------------------------------+
|Filename: SZX TEST 1.0.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=359|
|Filename: SZX Delivery Note 1.0.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=360|
+-------------------------------------------------------------------+



--
LiziC
This tested in the source file. So,add your filename and change b4 to a25
=OFFSET($G$1,SUMPRODUCT(--($B$4:$B$40=B4)*--(($C$4:$C$40)="Y")*--(ROW($G$4:$G$40)-1)),0)

Hi Don,

I can't get this Offset formula to work in my spreadsheet, where exactly does the filename need to go in the formula?

Thanks,

I think I'm being a pain switching between the Offset and If/VLookup formula's but neither seem to be bringing up the information I'm asking it for.

In the case of the Offset formula, I think I've worked it out for the supplier details but Im unsure of how to drag it to the other cells for PO number, Sku, Description, etc. And it's not entirely bringing the correct information across. It's ignoring all the 'BM' supplier fields and just entering the 'EG' and 'MR' but they don't match to the corresponding Sku on the SZX file.

And while I thought the IF/Lookup was working, it just stops working half way down the file and I can't figure out why.

I would attach the excel files again but even compressed as a Zip they are 134kb. Is there anywhere else I could upload them to show you wants going on?

I'm going to be honest, I prefer using the If/VLookup because I actually understand that equation but if the Offset is the best to use, as long as it works I'm going to be upset!!

As always, help is very appreciated as I'm definitely going above my Excel Skillset!!

Dear LiziC, Good Afternoon.

Save your worksheet at any free site, e.g. www.4shared.com and put the link here.

Hi Mazzaropi,

Here are the links to the files:

SZX
http://www52.zippyshare.com/v/43984583/file.html

DN MT (If /VLookUp)
http://www52.zippyshare.com/v/12653953/file.html

DN MT (Offset)
http://www52.zippyshare.com/v/96609640/file.html

Many thanks!