I am trying to create an org chart in Excel. Each box in the chart will have
a department, the number of employees and a budget amount. The numbers and
amounts will change with each hew fiscal year and even sometimes throughout
the year. Is there a way to have the chart on sheet 1 and the data on sheet
2? In other words, I would like each box in the org chart to be linked to
data on sheet 2. this way, I can just change the data on sheet 2 and the org
chart on sheet 1 will update. Thanks.

ajr81 wrote,

I am trying to create an org chart in Excel. Each box in the chart will
have
a department, the number of employees and a budget amount. The numbers
and
amounts will change with each hew fiscal year and even sometimes
throughout
the year. Is there a way to have the chart on sheet 1 and the data on
sheet
2? In other words, I would like each box in the org chart to be linked to
data on sheet 2. this way, I can just change the data on sheet 2 and the
org
chart on sheet 1 will update. Thanks.

If you have an org chart in Sheet1 and your data in Sheet2 you just need to
click on a text box in the org chart and enter a formula like =Sheet2!$B$1
to link the org chart to your data.

Let's say that in Sheet2 you have data arranged like this;

Cell B2 - "My Department"
Cell B3 - 126
Cell B4 - 250,000

Then enter the following formula in cell B1
=B2 & CHAR(10) & B3 & " Employees" & CHAR(10) & B4 & " Budget"

and link a text box in the org chart with the formula =Sheet2!$B$1

CHAR(10) is a line feed, it will not show in Sheet2, but will be rendered
correctly on the org chart.

Ed Ferrero
www.edferrero.com