[TedK1967]

I am using Excel 2007 and I am attempting to do the following:

I want to use a drop down box to automatically enter data into a worksheet.

Scenario:
I have 5 worksheets of schedules. I have 7 worksheets of daily schedules. On the Monday Sheet, I created a drop down list with Week 1, Week 2, Week3, Week 4, and Week 5. Whenever the user selects Week 1, I want the sheet to fill in the data from the first worksheet of schedules. When Week 2 is selected, the data from the second worksheet of schedules, and so on.

In the first cell I want to auto populate, I placed the following string: =IF(P3="Week 1",ScheduleW1!B5,"",IF(P3="Week 2",ScheduleW2!B5,""... and so on. It returns an error of you have entered too many arguments. How can I get this to work?

Any help is VERY appreciated.
Thank You.
TedK

[Advertisements]

=IF(P3="Week 1",ScheduleW1!B5,"",IF(P3="Week 2",ScheduleW2!B5,""... and so on. It returns an error of you have entered too many arguments. How can I get this to work?

I think there is a limit of seven nested IF statements... or there use to be... but it might just be you need a slight adjustment to your formula:

=IF(P3="Week 1",ScheduleW1!B5,IF(P3="Week 2",ScheduleW2!B5,IF(P3="Week 3",ScheduleW3!B5,IF(P3="Week 4",ScheduleW4!B5,IF(P3="Week 5",ScheduleW5!B5,"")))))

[Vino Rosso]

=IF(P3="Week 1",ScheduleW1!B5,"",IF(P3="Week 2",ScheduleW2!B5,""... and so on. It returns an error of you have entered too many arguments. How can I get this to work?

I think there is a limit of seven nested IF statements... or there use to be... but it might just be you need a slight adjustment to your formula:

=IF(P3="Week 1",ScheduleW1!B5,IF(P3="Week 2",ScheduleW2!B5,IF(P3="Week 3",ScheduleW3!B5,IF(P3="Week 4",ScheduleW4!B5,IF(P3="Week 5",ScheduleW5!B5,"")))))

[TedK1967]

OH MY GOODNESS!!!!!!!! THANK YOU SO MUCH. I thought you had to enter 3 arguments per IF statement. such as =IF(value,if true,if false. I noticed in your string, you omitted the last "" that I had entered for the (if false) arguement. I tried it and it works perfectly. Thank you very much.

[Vino Rosso]

You're welcome.

You do have to enter three parts to an IF statement, it's just that for the first statement, 'false' is everything else is the third part...

=IF(P3="Week 1",ScheduleW1!B5,IF(P3="Week 2",ScheduleW2!B5,IF(P3="Week 3",ScheduleW3!B5,IF(P3="Week 4",ScheduleW4!B5,IF(P3="Week 5",ScheduleW5!B5,"")))))

Likewise, for the second IF statement, everything bold is 'false'...

=IF(P3="Week 1",ScheduleW1!B5,IF(P3="Week 2",ScheduleW2!B5,IF(P3="Week 3",ScheduleW3!B5,IF(P3="Week 4",ScheduleW4!B5,IF(P3="Week 5",ScheduleW5!B5,"")))))

[Rediah]

Hello TedK1967 and Vino Rosso

Just my zero cents, it would be much easier to use LOOKUP function as the depth (iteration) of If function increases

[TedK1967]

One more question for the expert, lets say that all the above is correct and I wanted to add another IF command.
Example:
some of the cells in the schedule worksheet have no values. if it has no values then i don't want values on the daily schedule.

if P3="Week 1" AND ScheduleW1!B5="" then "" and if P3="Week 1" then ScheduleW1!B5
basically I'm trying to add an IF AND statement 5 times.

I tried the string:

=IF(AND(P3="Week 1",ScheduleW1!B5=""),"",ScheduleW1!B5,IF(AND(P3="Week 2",ScheduleW2!B5=""),"",ScheduleW2!B5)))

and it returns the same, too many arguments entered.

[Vino Rosso]

Hello TedK1967 and Vino Rosso

Just my zero cents, it would be much easier to use LOOKUP function as the depth (iteration) of If function increases

Yep, there's usually at least three different ways to do things in MS Excel.

One more question for the expert, lets say that all the above is correct and I wanted to add another IF command.
Example:
some of the cells in the schedule worksheet have no values. if it has no values then i don't want values on the daily schedule.

if P3="Week 1" AND ScheduleW1!B5="" then "" and if P3="Week 1" then ScheduleW1!B5
basically I'm trying to add an IF AND statement 5 times.

I tried the string:

=IF(AND(P3="Week 1",ScheduleW1!B5=""),"",ScheduleW1!B5,IF(AND(P3="Week 2",ScheduleW2!B5=""),"",ScheduleW2!B5)))

and it returns the same, too many arguments entered.

I'm not clear on what you're trying to do. If Week 1 is selected and ScheduleW1!B5 is blank, then 'blank' will be returned anyway.

[TedK1967]

Actually the cells are formatted to time, and even though it's blank on one of the schedule worksheets, it puts a value of 12:00AM.

[Vino Rosso]

OK, how about:

=IF(AND(P3="Week 1",ScheduleW1!B5<>""),ScheduleW1!B5,IF(AND(P3="Week 2",ScheduleW2!B5<>""),ScheduleW2!B5,IF(AND(P3="Week 3",ScheduleW3!B5<>""),ScheduleW3!B5,IF(AND(P3="Week 4",ScheduleW4!B5<>""),ScheduleW4!B5,IF(AND(P3="Week 5",ScheduleW5!B5<>""),ScheduleW5!B5,"")))))