some kind of IF or lookup???
 

Hi and happy new year. I have a workbook that is going to be used to
personnel projections. Big problem came up yesterday and I need to fix it
soon. The end user enters info about an employee and then assigns an
employee to a preset calendar. (There are 24 or so calendars from which to
choose.) On one of the worksheets, I have a huge IF & statement that looks
at the calendar for the employee to determine the number of days for the
first month and then moves across the spreadsheet for the next 23 months
accordingly. Everything is fine except now the powers that be want to give
people the ability to select either this year or last year as the starting
point. My IF & statement says its too big now and I can't add to it.
Combined problem is the fact that some program people have calendars that
start in different months. Any help would be appreciated as I've been
tearing my hair out since yesterday trying to think of a solution. Thanks in
advance.

some kind of IF or lookup???
 

Show us how the data is laid out, and your formulae.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SLP" wrote in message
...
Hi and happy new year. I have a workbook that is going to be used to
personnel projections. Big problem came up yesterday and I need to fix it
soon. The end user enters info about an employee and then assigns an
employee to a preset calendar. (There are 24 or so calendars from which
to
choose.) On one of the worksheets, I have a huge IF & statement that
looks
at the calendar for the employee to determine the number of days for the
first month and then moves across the spreadsheet for the next 23 months
accordingly. Everything is fine except now the powers that be want to
give
people the ability to select either this year or last year as the starting
point. My IF & statement says its too big now and I can't add to it.
Combined problem is the fact that some program people have calendars that
start in different months. Any help would be appreciated as I've been
tearing my hair out since yesterday trying to think of a solution. Thanks
in
advance.

some kind of IF or lookup???
 

There is a sheet named Calendars. Column A lists the names of the various
calendars we use. Column B through whatever are the months starting with Jan
07 and going to March 10. Rows 2 through the amount of rows for each of the
calendars lists the days each of the months has. (Hope that makes sense).
Its a grid to determine the number of days in a month for that calendar.

On the Employee Data sheet, there is a cell where the Program Director
enters the calendar that will be used for the employee. There are columns
next to that the contain the months of the Program based on the date entered
for the contract start on the information sheet.

Here is the formula:

=IF($AM5="July 07 to June 08",Calendars!F$2,"")&IF($AM5="July 08 to June
09",Calendars!R$3,"")&IF($AM5="Oct 07 to Sept 08",Calendars!I$4,"")&IF($AM5=
"Oct 08 to Sept 09",Calendars!AG$5,"")&IF($AM5="Jan 08 to Dec
09",Calendars!L$7,"")&IF($AM5="Apr 07 to Mar
08",Calendars!C$8,"")&IF($AM5="Apr 08 to Mar
09",Calendars!O$9,"")&IF($AM5="Agency",Calendars!U $10,"")&IF($AM5="Agency1",Calendars!U$11,"")&IF($A M5="Agency2",Calendars!U$12,"")&IF($AM5="Agency3", Calendars!U$13,"")&IF($AM5="Agency4",Calendars!U$1 4,"")&IF($AM5="Agency5",Calendars!U$15,"")&IF($AM5 ="Agency6",Calendars!U$16,"")&IF($AM5="Agency7",Ca lendars!U$17,"")&IF($AM5="Agency8",Calendars!U$18, "")&IF($AM5="Agency9",Calendars!U$19,"")&IF($AM5=" Agency10",Calendars!U$20,"")&IF($AM5="Summer",Cale ndars!R$21,"")&IF($AM5="Winter",Calendars!T$22,"") &IF($AM5="Empty",Calendars!L$23,"")

The 10 Agency calendars need to account for 2007 and 2008 but the formula
won't let me add more If & statements.

Help would be greatly appreciated. Thanks.



"Bob Phillips" wrote:

Show us how the data is laid out, and your formulae.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SLP" wrote in message
...
Hi and happy new year. I have a workbook that is going to be used to
personnel projections. Big problem came up yesterday and I need to fix it
soon. The end user enters info about an employee and then assigns an
employee to a preset calendar. (There are 24 or so calendars from which
to
choose.) On one of the worksheets, I have a huge IF & statement that
looks
at the calendar for the employee to determine the number of days for the
first month and then moves across the spreadsheet for the next 23 months
accordingly. Everything is fine except now the powers that be want to
give
people the ability to select either this year or last year as the starting
point. My IF & statement says its too big now and I can't add to it.
Combined problem is the fact that some program people have calendars that
start in different months. Any help would be appreciated as I've been
tearing my hair out since yesterday trying to think of a solution. Thanks
in
advance.

some kind of IF or lookup???
 

Hi, Wanted to let you know that I figured out a solution. I added 24 more
columns to do a check for Agency 2007 calendars and then in the IF & I showed
you, I added one more IF to check back on the new columns. This project is
huge. 15 spreadsheets and the Employee Data sheet only has about 12 columns
left! Enjoy the day and the new year. Thanks for your trying to help.

"SLP" wrote:

There is a sheet named Calendars. Column A lists the names of the various
calendars we use. Column B through whatever are the months starting with Jan
07 and going to March 10. Rows 2 through the amount of rows for each of the
calendars lists the days each of the months has. (Hope that makes sense).
Its a grid to determine the number of days in a month for that calendar.

On the Employee Data sheet, there is a cell where the Program Director
enters the calendar that will be used for the employee. There are columns
next to that the contain the months of the Program based on the date entered
for the contract start on the information sheet.

Here is the formula:

=IF($AM5="July 07 to June 08",Calendars!F$2,"")&IF($AM5="July 08 to June
09",Calendars!R$3,"")&IF($AM5="Oct 07 to Sept 08",Calendars!I$4,"")&IF($AM5=
"Oct 08 to Sept 09",Calendars!AG$5,"")&IF($AM5="Jan 08 to Dec
09",Calendars!L$7,"")&IF($AM5="Apr 07 to Mar
08",Calendars!C$8,"")&IF($AM5="Apr 08 to Mar
09",Calendars!O$9,"")&IF($AM5="Agency",Calendars!U $10,"")&IF($AM5="Agency1",Calendars!U$11,"")&IF($A M5="Agency2",Calendars!U$12,"")&IF($AM5="Agency3", Calendars!U$13,"")&IF($AM5="Agency4",Calendars!U$1 4,"")&IF($AM5="Agency5",Calendars!U$15,"")&IF($AM5 ="Agency6",Calendars!U$16,"")&IF($AM5="Agency7",Ca lendars!U$17,"")&IF($AM5="Agency8",Calendars!U$18, "")&IF($AM5="Agency9",Calendars!U$19,"")&IF($AM5=" Agency10",Calendars!U$20,"")&IF($AM5="Summer",Cale ndars!R$21,"")&IF($AM5="Winter",Calendars!T$22,"") &IF($AM5="Empty",Calendars!L$23,"")

The 10 Agency calendars need to account for 2007 and 2008 but the formula
won't let me add more If & statements.

Help would be greatly appreciated. Thanks.



"Bob Phillips" wrote:

Show us how the data is laid out, and your formulae.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"SLP" wrote in message
...
Hi and happy new year. I have a workbook that is going to be used to
personnel projections. Big problem came up yesterday and I need to fix it
soon. The end user enters info about an employee and then assigns an
employee to a preset calendar. (There are 24 or so calendars from which
to
choose.) On one of the worksheets, I have a huge IF & statement that
looks
at the calendar for the employee to determine the number of days for the
first month and then moves across the spreadsheet for the next 23 months
accordingly. Everything is fine except now the powers that be want to
give
people the ability to select either this year or last year as the starting
point. My IF & statement says its too big now and I can't add to it.
Combined problem is the fact that some program people have calendars that
start in different months. Any help would be appreciated as I've been
tearing my hair out since yesterday trying to think of a solution. Thanks
in
advance.