Link to home
Start Free TrialLog in
Avatar of Jase Alexander
Jase AlexanderFlag 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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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?
Avatar of 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
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.
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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Rob

Perfect !!

Thank You for your time

Jase
THank you this worked perfect !
Happy to help!