Excel Lookup entire Workbook

[KsMaverick]

Hi Team
I have multiple worksheet on 1 excel file that has 5 columns ([A]Part number, Description, [C]QTY, [D]Approval number, [E]validity). I usually need to check what is the approval number per each part number and it has to be active as well.

At the moment my first worksheet (ALL) is just a place I enter the part number and it shows me all the details, I do it by using IF function for each column on the other worksheets
[ on column I {=IF($E2>=TODAY(),IF(ALL!$D$3=$A2,A2,I3),I3)} & {last cell will be =next sheet first cell on column I }]
I want to be able to remove this IF function has other people will start using this excel sheet and when they add a new worksheet with new part numbers and approval numbers, if they do no copy the IF function it will not work.

Is there any way I can get it to work with Vlookup or run a macro?
Also to find a way to enter all the need part number row vise in worksheet ALL and see all other details automatically show up.

 

[KsMaverick]

([-A-]Part number, [-B-]Description, [-C-]QTY,[-D-]Approval number, [-E-]validity)