How do I use a tab name and cell reference in a formula?

[JillieB]

I have a spreadsheet that pulls in values from another file. My question involves using a Tab name in a formula.

I use my Total Order spreadsheet to total up my food ordering sheet based on what menu days are coming up. (We have 32 menu days, then start over again with Day 1. We only need to order 9 days worth of food at a time.) I have another spreadsheet file that has a different sheet for each Menu Day, and each tab name is named Day 1, Day 2, etc. and lists each food product and the ordering par needed. My formula currently in the Total Order spreadsheet, for Wednesday for example, is
=[Daily Ordering Sheets.xls]Day 5'!$E8
next to the product item in row 8, for example, which would be Ground Beef. I currently use the Find and Replace to change each formula (i.e. - Find - Day 5 - Replace - Day 26)

Is there a way that I can type 'Day 26' in cell A1 on the Total Order spreadsheet and then change my above formula to look at cell A1 for what Day it is, instead of using the Find and Replace?

 

[VoG]

Try

=INDIRECT("'[Daily Ordering Sheets.xls]"&A1&"'!$E8")

 

[JillieB]

Thanks VoG, that formula worked! (I used $A$1 in my formula so when I copied and pasted, it would still look to the correct cell for the menu day.)

However, when I tried to copy and paste that formula to the other 300 rows in that column, it kept the $E8 in the formula for all the cells instead of changing the row number. I even tried taking the $ sign off the E8, but it didn't change anything, which I figured it wouldn't since that just indicated the column. It still kept 8 in their as the row # for all 300 rows. Any thoughts?