Example: I have a workbook with 3 sheets: Report, DeptA, and DeptB. When I
change the value in the cell named Dept in Report to "A", I want the other
cells in Report to retrieve values from DeptA spreadsheet, and when I enter
Dept="B" I want the values from the DeptB spreadsheet.

In other words, I want the reference to the other worksheets to change based
on the value in one cell. Can I do this by combining formulas or do I have to
use VBA?

I tried something like [="Dept"&(dept)&!A1] (if A1 was the cell I wanted),
but got problems combining text and formula. I know I could use IF, but not
if there are 20 different sheets. Besides, shouldn't there be a more elegant
way to do this?