In cells A40 - A47, the following formulas are contained:
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 1-200'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 201-400'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 401-600'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 601-800'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 801-1000'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 1000-1200'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 1201-1400'!$F$1:$G$700,2,FALSE)
=VLOOKUP(B3,'[Ebay Stock Sheet.xlsx]LISTINGS 1401-1600'!$F$1:$G$700,2,FALSE)

The purpose of these formulas is to search an external workbook (Ebay Stock Sheet.xlsx) to find out quantities of stock.
The search criteria is product codes, e.g V000130390 which is contained in column B on the workbook I am working on.
Once the product code in for example 'B3' is found on the external workbook, it returns with the quantity remaining of that product.

=SUM(IF(NOT(ISERROR(H5:H12)),VALUE(TRIM(SUBSTITUTE(H5:H12,CHAR(160)," ")))))

That is the array formula that links all the vlookups together, so if the product code being searched for is not in LISTINGS 1-200, it then searches LISTINGS 201-400 etc until it is found.

The formulas all work well - The only problem I am facing is that when I try to drag down the formula:
=SUM(IF(NOT(ISERROR(H5:H12)),VALUE(TRIM(SUBSTITUTE(H5:H12,CHAR(160)," ")))))
The results stay the same all way down - It is continuously searching for the product code in B3.

I want it to search for the product code in B4, then for B5, then for B6 etc. and return the quantity remaining for each product code.

Online I have seen people saying that it would be easier if all the sheets were merged together which would be all good and would work perfectly. HOWEVER the data on the 'Ebay Stock List.xlsx' is live data pulled from ebay using the 'From Web' tool in excel - And you can only have 1 data pull from the web on each sheet, hence why that idea wouldn't work.

Which is why I need the formula (whatever formula that may be) to search through all the sheets - And I need to be able to drag the formula down so that it changes the criteria for each cell - otherwise it would be very time consuming!

I am sure there is a way to do this and I am incredibly close to fixing it now - I just need a way to make the array formula able to drag down and complete all the cells for me.

I have tried to go into detail as much as I can to make it easier for people to help me.
If there is anything else you need to know about the workbooks/sheets just ask

Thanks
James