Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel Drop Down List


  • Please log in to reply

#1
TedK1967

TedK1967

    New Member

  • Member
  • Pip
  • 4 posts
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
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts

=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,"")))))
  • 0

#3
TedK1967

TedK1967

    New Member

  • Topic Starter
  • Member
  • Pip
  • 4 posts
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.
  • 0

#4
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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,"")))))
  • 0

#5
Rediah

Rediah

    Member

  • Member
  • PipPipPip
  • 259 posts
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
:)
  • 0

#6
TedK1967

TedK1967

    New Member

  • Topic Starter
  • Member
  • Pip
  • 4 posts
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.
  • 0

#7
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts

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.
  • 0

#8
TedK1967

TedK1967

    New Member

  • Topic Starter
  • Member
  • Pip
  • 4 posts
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.
  • 0

#9
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
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,"")))))
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP