Massive =IF headache

**paradox34690** · 03-22-2012, 08:13 PM

Hi everyone. I'm new to the forums; pretty sure I'm posting in a proper area for this...

Here's my situation:

I'm working on a scheduling spreadsheet for my company that I'd like to pretty much have "automated" (not using VBA unless needed) by using just pasting in information and the magic of formulas, etc. This entire process was originally done using information from single worksheets on MULTIPLE spreadsheets (like 4 or 5 total files). Because someone, somewhere along the lines was completely lazy and never consolidated the required worksheets into 1 single Excel spreadsheet, the task has fallen on my lap to accomplish. Keep in mind, none of this was created by me. I'm working with what was given to me - i know nothing about how to work pivot tables or set them up, i'm awesome tho at math so this project has a certain amount of appeal to me........

The spreadsheet I'm creating has the individual worksheets from the original files already setup. I've got a good portion of stuff figured out already, but i'm hitting a roadblock here. I'm going to post the .xls file as well so you can SEE what I'm trying to accomplish.

5 worksheets total; however only 4 of them are needed for this issue. The first worksheet is called "Input" and is used to paste in what I'm going to call my "seed information". The second worksheet is called "Output" and calculates the total number of hours to be logged for various TV channels using a pivot table. The third worksheet is called "Data Production Night Staff" and contains 8 columns; A2:A43 is listing positions, B1:H1 are labeled "Sunday" through "Saturday"... B2:H43 contains names of employees. Worksheet 4 is "Top Sheet" - This sheet is where my final product is to come together and where I'm having the massive headache...

I've been trying to come up with the correct =IF formula so that when data is pasted into "Input", everything is figured out. The only thing that matters is the day of the week. I have an =IF statement that does not work supposedly because nested =IF statement can only be 7 levels deep.... Um... ok? Unless I'm counting wrong, this is ONLY 7 levels:

Please Login or Register  to view this content.

What I'm trying to accomplish is something like this; If it's day 1 of the week, I want to populate specific cells on the "Top Sheet" with data from B2:B43 on "Data Production Night Staff"; if it's day 2 of the week, populate with data from C2:C43, etc to Day 7 using data from H2:H43.

I'm considering this my magic line of code.. Once I have this one single formula (or method) solved, I can wrap this up. If I truncate the formula to this:

Please Login or Register  to view this content.

it works, but it will only do 6 columns...

Help! :D

**paradox34690** · 03-22-2012, 08:19 PM

Holy crap, I'm retarded....

like, the second I hit "Save"..... if today <> 1-6, then it MUST be 7!!

omg... okay, I'm going home....

**FDibbins** · 03-22-2012, 08:31 PM

gotta love it when you figure it out for yourself

**rylo** · 03-22-2012, 08:34 PM

Hi

Consider the CHOOSE function.

Please Login or Register  to view this content.

rylo

**Pete_UK** · 03-22-2012, 08:34 PM

First thing - your caption says that you have Excel version 2010, although the file you have attached is an .xls file (i.e. 2003 or earlier). If you save your file as .xlsx then this will overcome the nesting limit.

Second point - remember that it is IF(condition, action_if_true, action_if_false), and also that WEEKDAY(TODAY()) can only return the values from 1 to 7. Thus you can test for 6 of those values using the IFs and if none of them are true then it must have the value of 7. So, you can write your second formula like this:

=IF(WEEKDAY(TODAY())=1,'Data Production Night Staff'!B2,IF(WEEKDAY(TODAY())=2,'Data Production Night Staff'!C2,IF(WEEKDAY(TODAY())=3,'Data Production Night Staff'!D2,IF(WEEKDAY(TODAY())=4,'Data Production Night Staff'!E2,IF(WEEKDAY(TODAY())=5,'Data Production Night Staff'!F2,IF(WEEKDAY(TODAY())=6,'Data Production Night Staff'!G2,'Data Production Night Staff'!H2))))))

However, it is always better to avoid lengthy multiple-IF formulae like that. You can replace all that using this:

=INDEX('Data Production Night Staff'!B2:H2,WEEKDAY(TODAY()))

Hope this helps.

Pete

**paradox34690** · 03-22-2012, 08:38 PM

Actually Pete, I don't want to sound out of line here, but no, it doesn't overcome the nested limit. It's currently in xls format because some of our peeps here don't have 2010. I do. I tried it last night thinking the exact same thing, however, it still didn't like me....

You guys are AWESOME tho... omg, first off Rilo, that's incredible! I didn't think it would work like that....

and Pete.... seriously.... that's brilliant!

thank you so much!! (Even you FDibbins!)

:D

**Pete_UK** · 03-22-2012, 08:53 PM

Well, whenever you think you need to have multiple-IFs, think about VLOOKUP, HLOOKUP, or INDEX (possibly in conjunction with a MATCH function). You could also, in this instance, have done this:

=INDIRECT("'Data Production Night Staff'!"&CHAR(65+WEEKDAY(TODAY()))&"2")

though you would need a slight change to the end if you want to copy the formula down and get data from rows other than 2.

If you get chance, please mark the thread as SOLVED (the FAQ shows how), and you can also click on the "star" icon in the bottom left corner of any post that has helped you, in order to pass on thanks directly to the poster.

Pete

**paradox34690** · 03-22-2012, 09:08 PM

@Pete - I thought about trying VLOOKUP and all that, but quite frankly, it scared me.

Actually, I'm the IT person for this company. Simple is best right now, and so far, you win. Didn't want to get into making it way more complicated than needed..... I'll be posting back about this same spreadsheet to a different thread in a little bit.

Drop down boxes containing the data from B2:H43 per day to populate cells on the "Top Sheet" so that managers have ultimate say about who is where, etc....