Populate Field Using Dependent Data Validation Lists

**warrior2411** · 09-13-2012, 01:58 PM

Greetings,

I created 4 different tabs (‘Work’, ‘School’, ‘Home’, Volunteer’) with the same type of information (Date, Hours of Homework, and Length of Break) on each tab. For further reference, see the attachment for example.

Additionally, I created dependent data validation lists.

Work:
School
Work

Personal:
Home
Volunteer

When you click under “select area” you are given either work or personal. When you choose either, under “select location” you can either choose School, Work, Home, or Volunteer based on your previous selection.

My questions are:

(1) How do I create formulas that list the respective information (date, hours of homework, and time length of break) from the selected tab?
For Example: If I select Volunteer, I want all data to populate in the blank field on the ‘Report’ tab.

(2) Once the data is populated and I am able to toggle back and forth between my 4 choices, is there a way...

a. To create a line chart (graph) that displays hours of homework AND time length of break?

b. To create a second graph that allows you to compare from separate locations from separate area?
For Example: I want to compare the hours of homework from School (from WORK) and Volunteer (from PERSONAL)

This is a pretty in-depth request. Any feedback is much appreciated. Thanks!!!

‘Example(d).xlsx

**JieJenn** · 09-13-2012, 02:10 PM

In A9 enter

=IF(INDIRECT($B$6&"!"&ADDRESS(ROW()-7,COLUMN()-1))="","",INDIRECT($B$6&"!"&ADDRESS(ROW()-7,COLUMN()-1)))

Forgot to add:

Drag it down and across.

**warrior2411** · 09-13-2012, 02:21 PM

JieJenn,

That formula works like a charm! Your help is much appreciated!

Would you happen to know the second part of my question?

Can I create a second graph that allows me to compare from separate locations from separate area?
For Example: I want to compare the hours of homework from School (from WORK) and Volunteer (from PERSONAL)

**JieJenn** · 09-13-2012, 02:28 PM

I am at work right now so Probably won't be able to answer your question #2 at the moment. But create a new post and I am sure someone else will be able to help with your question.