Importing all column values from 2 excel files to single column on 3rd Sheet

Hi There,

I am trying to import data from 2 excel sheets to a single third one . This is what I am trying to do:

1. Import data ( row 2 to 1000) from Column C in Sheet X to Sheet Y Column B ( row 1 to 1000)
2. Import data ( row 2 to 500) from Column E in Sheet Z to Sheet Y Column B ( row 1100 to 1600)

Point 1 works fine with no issues , However when I try Point 2 it does work if I try single value but when I give the range it gives me data error .

is there any way I can do this ? the Sheet X & Z gets updated every 3rd day and all these sheets are in the same folder.

this is the formula I am using -
='C:\[SheetX.xlsx]Costs'!$C$2:$C$1000 ( works)
='C:\[SheetZ.xlsx]Cost2'!$E$1 ( Works)
='C:\[SheetZ.xlsx]Costs2'!$C$2:$C$500 ( doesn't work)

Thanks a ton for the help .

Plz post your sample file with required result.