How to combine IF statement in OFFSET function?? Please Help

Hello!

Please see my workbook attached to this post.

I am working on a formula and I can't figure out how to combine the IF fucntion inside the OFFSET function to make it work properly. The file is a timesheet where I want to fill out the timesheet with task I've done through out the day (This have all the task for other people too).

This is what I want it to do:
1. Select initial under "Name"
2. Once name is selected it will show 5 tasks from my short list/"Full List" under "Task" worksheet (If I pick a different name it will the 5 items from that persons short list) so under the day the following options is what I want it to appear, Task5, Task10, Task16, Task19, Task21, and "Full List"
3. I can pick any of those 5 tasks, or if i click on "Full List" it will show all the task for that person, and so forth for other persons

(I want to be able to pick any name, and then it will show me their own short list, and "Full List" for their task only. I don't want to pick a name but then show ALL the tasks because i'll have over 100 tasks on the list, just what their task is only). Is this possible to do??

I cannot combine the IF statement for the "Short List"/"Full List" into the OFFSET formula. Please see "Data Validation - Swith Lists" for the IF formula.


Please help me with this problem, much would be appreciated

Thank you!!

Wow thank you sir, you're a genius. I have no idea how you did that formula but it works! thnx!

Hi JBeaucaire,

I have a problem with the worksheet, when I try to enter the real task into the excel sheet in the "Task" worksheet the string is broken and it doesn't show up on the drop down menu. How to fix this? and also in your code how did you reference it back to the "Task" worksheet when your on the "Master Timesheet"? Also how to move the "Full List" option last e.g. Task1, Task2, Task3, Task4, Task 5, Full List, instead of Full List at the begining?

Please Login or Register  to view this content.

Please help

Hi JBeaucaire,

Could you take a look at the spreadsheet again, I do not know how you defined Shortlist in "Index" function. As you can see the list becomes broken and unselectable after I enter the actual tasks that i'm suppose to do. Could you explain to me how you define it because I am not very good at excel functions.

Kind Regards,

Yeah, your original sample made it appear we could use the A.K code in the COUNT formulas for the ShortList and the FullList, but then you took them out. So we can't use them.

You can delete the named range ShortList, we won't need it, and redefine the FullList as column A on the Data Validation sheet. Then you can switch the data validation to this:

=IF(D12<>"Full List", OFFSET(INDEX(FullList, MATCH(B12, FullList, 0)),,1,COUNTA(OFFSET(INDEX(FullList, MATCH(B12, FullList, 0)),,1,COUNTIF(FullList, B12))),), OFFSET(INDEX(FullList, MATCH(B12, FullList, 0)),,2,COUNTIF(FullList, B12),))

Hi JBeaucaire,

Sorry but your formula is too complex for me to understand, I do not know what you mean. Everytime I want to add an item in (Insert a row) the data shifts down one and the table doesn't look up all the data. So I could not add new tasks or new users into the spreadsheet because everytime i do it the formula stops working. I tried to play with it but I have no clue what to do. I'll have to add few more people and maybe 30 more tasks onto the table so it becomes a problem when I don't know how to fix the formula to give the range for look up, or when i delete a task or user it also stops working.

Could you explain how to reference when i need to add/delete new users/tasks on the spreadsheet, explain it like I'm a 5 year old kid (Micheal from "The Office")

Thank you so much!
Plz bare with me.

Hi JBeaucaire,

I finally figured out how to fix the issue, thank you so much for your help!!! +rep!!

I got another question, say there are 4 divisions

1. Finance
2. Marketing
3. Human Resource
4. Accounting

Then say I fill out my timesheet and for example all the Task #10 for all users belongs to Finance, Task #15 belongs to Marketing, Task #16 belongs to HR, and Task # 18 belongs to accounting. So my question, is there a formula where I can fill out at the bottom of the page under Finance, Marketing...etc that can identify the Tasks that I fill out on the timesheet and automatically calculate .25 for each certain tasks that are fill out into the specific cell?

I could have the users caculate their own hours for their tasks that goes into what division, but the problem is say the task #10 is suppose to be under Finance but John Doe puts it as Accounting. So if they put it in the wrong task under wrong division the allocation is incorrect. I want to minimize as much human error as possible. Please see attached for example

Is there such a formula for this?

Thank you!

Yes that formula seems to work, but how do i combine multiple tasks together? for example if "Monitor Bank Acconts" & "Process Month End" & "Reconcilliation" belongs to Accounting how do I combine multiple task togeter in one formula. I'm trying to combine the "COUNTIF" and the "AND" functions together and it doesn't seem to work.

Please Login or Register  to view this content.

Could you help me refine this?

Thank you!

I don't see what you mean. Perhaps adding the 2 digit code to the end of those task titles would be easiest?

Or

=SUM(COUNTIF(D12:D65,{"*Monitor Bank Accounts","*Process Month End","*Reconcilliation"}))*0.25

Haha JB you know me too well :P

The formula you have made on the page works great! But one problem I ran into is I tried to recreate your formula on my master time sheet that I have with the list manager and everything for the 4 divisions except they are named differently that's all. I copied your formula over and it does not work on my worksheet. How do you define the "Accounting", "Marketing" etc... in the formula to relate to the list? I also notice you have { } in the SUM formula, what does this mean and what does it affect? Please show me how to recreate your formula on my sheet since it does not work when I copied it over.

Thanks JB!

Originally Posted by JBeaucaire

Don't let his question fool you, Cutter, his actual tasks won't have #'s like that in them, he got me with that earlier.


I've created 4 new "named ranges" on your LIST sheet. Just drop the "tasks" into each column for the category that it goes with, the named range is dynamic and will expand itself. Notice the Human Resource one is named Human_Resource.... no spaces in named ranges.

Then on the Master I've added some array formulas at the bottom.

=SUM(--ISNUMBER(MATCH(D$12:D$65,INDIRECT($A68),0)))*0.25

Perfect it works Thanks JB!