=IF(ISBLANK(Sheet2!A1),IF(ISBLANK(Sheet3!A1),IF(IS BLANK(Sheet4!A1),"What the heck, no data!",Sheet4!A1),Sheet3!A1),Sheet2!A1)

This will return the value, text or otherwise, from Sheet2 unless nothing is there. Then it will do the same for Sheet3 and Sheet4. If there is data in more than one sheet, it will return it from the "lowest" sheet and the others are ignored. If none have data, it returns a snarky comment.