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?

Indirect. See:
http://www.officearticles.com/excel/...ft_excel.h tm
************
Anne Troy
www.OfficeArticles.com

"Ms.Vahl" wrote in message
...
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?

I knew it had to be an easy way! Thanks!


"Anne Troy" wrote:

Indirect. See:
http://www.officearticles.com/excel/...ft_excel.h tm
************
Anne Troy
www.OfficeArticles.com

"Ms.Vahl" wrote in message
...
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?