Avatar of Jase Alexander
Jase Alexander
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel Extracting data from sheet depending on availability

Hi Experts

Hope you can help

I have a packing list template that pulls various information based on what is pasted into two separate sheets

It currently pulls information from two sheets – Paste Invoice & Paste Manual Excel Pick Sheet

What I’m struggling to do effectively is that the main body of the packing list pull the data primarily from the Paste Manual Excel Pick Sheet tab and if there is nothing available in there, then it reverts for the Paste invoice sheet

At present, my formula isn’t recognising beyond line 70 on Paste Manual Excel Pick Sheet when I delete the data on the Paste invoice tab

Is there an easier way of doing this?

Any suggestions would be very welcome
J
EE-Example.xls
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Rob Henson

If I read correctly, you are having issues with your formula from row 80 on Packing List:

=IF(AND('Paste Invoice'!A70<>"",'Paste Manual Excel Pick Sheet'!B71<>""),'Paste Manual Excel Pick Sheet'!B71&'Paste Manual Excel Pick Sheet'!C71,'Paste Invoice'!A70)

So this is saying if Paste Invoice A70 and Paste Manual B71 BOTH contain values (neither is blank) then join Paste Manual B71 & C71 otherwise use Paste Invoice A70.

On this particular example Paste Invoice A70 is blank so the AND condition (both contain values) is false so the IF statement is using the second option Paste Invoice A70 which is blank so result is 0.

What are you expecting to happen?
Jase Alexander

ASKER
Hi Rob

Thank you for the reply and I agree which is the conundrum Im facing

Im not sure what Im doing wrong I need to amend the formula to say if there is data in the Paste Manual Excel Pick Sheet then extract this however, if not, then extract the data from Paste Invoice

At present, as you suggested, I cannot get it to go beyond A70 as my TRUE FALSE combination I believe is incorrect and would welcome any suggestion as to correct this?

Thanks
Jase
Rob Henson

Still not 100% sure what you are expecting as a result.

In the meantime while I figure it out, your formula in column A doesn't make sense:

=IF(AND(B80="",B80="0"),"",$A$9)

B80 can never be "" AND "0", it can only be one or the other or something else. I think you need an OR statement rather than AND:

=IF(OR(B80="",B80="0"),"",$A$9)  

ie IF B80 is blank OR equal to 0 then result is blank otherwise use $A$9. This would then make sense for making the rows blank when not required because column B is blank or 0 result.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jase Alexander

ASKER
Hi Rob

Thanks again for your help

I apologise for the way in which Im describing my dilemma

Basically if you opent the file, it is currently extracting the data from the Paste Manual Excel Pick Sheet on the packing list template however it stops half way down whereas the data on the sheet continues past line 70 on the Paste Manual Excel Pick Sheet

I need it to continue extracting the data from this sheet UNLESS there is nothing (i mean blank cells)  in the Paste Manual Excel Pick Sheet and therefore, it reverts to extracting the data drom the Paste Invoice sheet

Hope this is a better clarification

Regards
Jase
ASKER CERTIFIED SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jase Alexander

ASKER
Hi Rob

Perfect !!

Thank You for your time

Jase
Jase Alexander

ASKER
THank you this worked perfect !
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

Happy to help!