Changing offset function to index; first cell working but the rest is not

[ASalazar]

Hi guys,

I am working on a fairly basic labor tracking sheet that is linked to two separate workbooks. I have attached one of the source workbooks here along with the labor worksheet. The labor worksheet is the destination workbook and the FOH Schedule worksheet is the source workbook. I used the OFFSET function for the cell references but once I realized that the destination workbook would not update unless the source workbooks were open I knew I had to change from the OFFSET function. I changed the formula in A6 on the labor worksheet from OFFSET TO INDEX and it worked just fine, although when I tried to drag the equation down the column the other cells were not functioning and are now displaying #REF!. Can someone help me to adjust the equation so that it works for all of the cells?

This was the previous formula:

=IF(OFFSET('[FOH SCHEDULE.xlsx]WEEK 1'!$A$5,(ROW(A1)-1)*2,0)=0,"",(OFFSET('[FOH SCHEDULE.xlsx]WEEK 1'!$A$5,(ROW(A1)-1)*2,0)))

This is the current formula that is working for the first cell but not the rest:

=IF(INDEX('[FOH SCHEDULE.xlsx]WEEK 1'!$A$5,(ROW(A1)-1)*2,0)=0,"",(INDEX('[FOH SCHEDULE.xlsx]WEEK 1'!$A$5,(ROW(A1)-1)*2,0)))

[JohnTopley]

INDEX expects a range e.g $a$5:$a$100 rather than one cll ($A$5) plus (ROW(A1)-1)*2 will return an index of 0 for the first line.

post a workbook - HOW TO ATTACH YOUR SAMPLE WORKBOOK: yellow banner at top of threads.