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
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
Last Comment
Rob Henson
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
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"),""
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"),"",
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.
In the meantime while I figure it out, your formula in column A doesn't make sense:
=IF(AND(B80="",B80="0"),""
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"),"",
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.
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
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
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.
ASKER
Hi Rob
Perfect !!
Thank You for your time
Jase
Perfect !!
Thank You for your time
Jase
ASKER
THank you this worked perfect !
Happy to help!
=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?