If the sheet names are like 5409 Smith you won't be able to use Sheet2!D4 as this as you found out will return a #REF! error

http://www.bettersolutions.com/excel...O318313331.htm

You need to change to ='5409 Smith '!D4 or use a indirect function to reference the sheet

http://www.bettersolutions.com/excel...E813622028.htm

HTH

VBA Noob