HI


I have a workbook designed as a performance review tool. It has multiple tabs with cells having formulas connected to a totals worksheet. Depending on the values assigned to these cells (mostly drop down boxes) the totals worksheet calculates an overall score. The problem, the person being evaluated and the evaluator are not the same person and these 2 separate workbooks are worked on by each individual until the time of the performance review when the two workbooks "come together" for the final evaluation. I am unable to move or copy the goals tab while keeping the functionality of the totals tab intact.

Can someone help and point me in the direction of how to do this: move or copy one tab of the workbook into a different instance of the same workbook and keep all the functions working. I've tried the usual, the move/copy tab with the source and destination workbooks both open, no glory. I've attached a copy of the workbook. Or any other ideas you might have.

thanks in advance

Have you tried linking the formulas to the other worksheet? I doesn't look like something you would use to record multiple answers for multiple employees. If it's multiple, you'll need to assign a unique name to each evaluated person's spreadsheet, and create the formula to add all the spreadsheets and their specific cell content together in the Total tab. I haven't done this in years, and can't recall what the pitfalls are, but that might give you a starting point to see if you can come up with something that works for you.

However, if it's a one-to-one ratio, evaluated/evaluator, just activate the formula in the Total tab of the Evaluator spreadsheet, and click the appropriate cell in the Evaluated spreadsheet to record its contents, and it should work. You may need to tweak it a bit, but that's where I'd begin.

Thank you for your reply. It is a one to one relationship, the workbooks are not linked. I want to move the completed goals tab from the "evaluated" to replace the goals tab in the "evaluator" workbook.

I'm not sure what you mean by activate the function in the totals tab. When I copy the goals tab from one workbook to the other, the totals tab "breaks". I've looked to see if all I need to do is refresh the workbook somehow to get the totals tab working again.

The current workaround has been to copy and past each goal from the goals tab into the evaluator's copy.

Go to the totals sheet, and click a cell that contains a formula. That "activates" the formula. Now display the source file, and click the cell that contains the data you're tracking in the Totals tab. The formula in the Totals cell you activated now shows the file name and cell location of the data source. When you click Enter, the cell with the formula displays the data from the other file. Like I said, you'll have to tweak it somewhat. Maintain your formala syntax, but take note of the "address" of the second file and cell location, and use that.

Try a simple test, and just create a SUM formula in any cell, then open another spreadsheet and click a cell that contains a number. Your first spreadsheet displays the content of the cell in the second worksheet. When both spreadsheets are open at the same time, the formula dynamically captures any changes you make to the source data in the second worksheet.

I wouldn't copy because you're copying a formula that is looking for data from another worksheet or tab. The formulas can't find the worksheet they're looking for because they're now in a different place. Instead, make the formula in the first file look for the data in the file where that data lives - in the second file.

Actually, I was saying that from memory, and when I did a quick test it didn't work. There is a way to link worksheets, though. When I get a chance I'll play around and see if I can remember how I did it.