Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

**Tbenz** · 01-30-2021, 10:03 PM

I have a schedule that has a work breakdown structure (1 , 1.1, 1.1.1, 1.1.1.1, 1.1.1.2, 1.1.1.3, 1.1.2, 1.1.2.1, etc)
These are grouped based on their blocking:
new project: x
sub project: x.x
sub sub task: x.x.x
action: x.x.x.x

From my daily schedule it pulls out what action is being worked on. Therefore, for any given day, I can evaluate the range x.x.x.x to see if something is happening, and place an "X" in the x.x.x row, so that way at a quick glance you know whats happening.

Currently, from what I have attached, to evaluate the entire project, I go from x to ROW(x+1)-1, so if ANYTHING is happening on that project, "X". Call ROW(x+1)-1 landing on A59. If I insert another sub project or sub sub task, A59 gets bumped down to, say, A79. HOWEVER, currently my (IF(LEN(A1:A59))>0, "X", "")) will not update to reflect the increase to A79. This means I can break my schedule if I forget to manually extend the range. How can I make this dynamic?

**Glenn Kennedy** · 01-31-2021, 07:04 AM

I THINK I follow what you want....

See the yellow row (the only place where I put a formula)

=IF(SUMPRODUCT(--(LEN(C9:INDEX(C9:C$100,MATCH(2,LEN($A9:$A$100)-LEN(SUBSTITUTE($A9:$A$100,".","")),0)))>0))>0,"X","")

The rows immediately following the formula (rows 9++) all contain 4-part IDs: 1.2.3.4. If I understand you corrrectly, you want to know if there are entries in any of the cells in that form, until you hit the next row with a 3-part ID (1.2.3).This looks down column C from C9 until the NEXT last row with a 4-part ID.

1. Enter some text in any of ther pale blue rows. Blanks in the yellow row will change to X.

2. Enter some text in the bright green row, where the correspnding yellow row in blank. Nothing happens.

3. Select the bright green row. Insert a couple of rows above it and copy your monster formula in column A into the newly created cells in col A.

4. Enter some text in the pale blue area of the newly created rows, where the yellow row is still blank. An "X" will appear.

Is that what you had intended?

**Tbenz** · 01-31-2021, 01:27 PM

Close!

I've decided to upload the actual document to ensure things work out.

You had the correct idea, but when I copy and pasted to 1.1.2 it didn't work for 1.1.2.x.

**Tbenz** · 01-31-2021, 01:27 PM

I'm being blocked from posting the rest of my response due to SQL injection but don't know where so I'll parse out the message here:

From my uploaded schedule:
Schedule area at the top that's light red: Enter in "SearchTerm"&_*hours* (eg. 2660_x_Various_Fit_7.0) under Jake.

You will automatically populate in that 'day column':
- Jake working on action fit in 1.1.1.1
- Summary of 1.1.1.x in 1.1.1 = "Fit" (Only action occuring this day)
- Summary of 1.1.x in 1.1 = "X" (Currently done with length function
- Summary of 1.x in 1 = "X" (Currently done with length function

**Tbenz** · 01-31-2021, 01:28 PM

So i've currently manually defined 1.1.1 to look at the manually defined range that represents 1.1.1.1 to see if anything is happening.

But you can see in G25 That as the result of a new action addition, my range is 1 off (doesnt include G37). This is the problem that exists in this area of the work sheet and I would like to resolve.

**Tbenz** · 01-31-2021, 01:29 PM

1) If I add a new action (1.1.1.1) as mentioned above, the range considered in 1.1.1 does not automatically change
2) If I add new subtasks (1.1.1) to create 1.1.2 (Effectively all tasks between 1.1 and 1.2), the range does not automatically update
3) Similarly, if I add new tasks (1.1) to create 1.2 , the range does not automatically update to look at all data between 1 and 2.

**Tbenz** · 01-31-2021, 01:29 PM

Seems to be the last couple lines were the issues but I dont know why.

So what I have in (E 24 to E 25) DOES work for this current application, but if I insert or add anything, then it falls apart as i have to manually recreate the range i'm looking at.

**Tbenz** · 01-31-2021, 01:30 PM

I know this is a pain to explain but if you use the group buttons and look at what I currently have in (E 24 to G 26) you should be able to see what's manual and where things fall apart if I were to go ahead and add more projects

**Tbenz** · 01-31-2021, 01:33 PM

I've made this attachment a little clearer with the goal in column E

**Glenn Kennedy** · 01-31-2021, 02:04 PM

Look at cell G24... it is looking down to G36 (it should be G37, but that's immaterial for now). and at cell G25, which is ALSO looking down to G36. Why do you need BOTH of them? I ask, not only because the one in G24 is providing you with NO additional information - but also because the one in G24 causes me some formula problems.

**Tbenz** · 01-31-2021, 05:44 PM

End of Day, I have it all grouped so its collapsible so I can navigate the over 30 jobs were working on. So the goal is a quick glance,

Forward looking:
If I look at project level (x), I want to see what days (columns )its being worked on
If i open that I want to see when every item is being worked on (the x.x level)
When I open that I see (x.x.x) and when thats being worked on
and all of the above is driven by the x.x.x.x level, or the action level.

So if my project is project 1, with items 1.1 1.2 1.3, I either :
- need to evaluate whether there is an "x" in 1.1 1.2 and 1.3 and if there is, something is being worked on, and show x on the project (X) level. This is of course again, driven upward from the x.x.x and x.x.x.x levels.
- need to evaluate the entire range between 1 and 2 (many redundancies here as i'm measuring every level but each of those levels also measureing the level below it. So this is what I currently have and is what youre having problems with. So I dont need to keep it that way. It's just at this point I was stuck and me doing the entire range and including those redundancies was easier.

**Tbenz** · 01-31-2021, 07:35 PM

I've found a work around.

I do the same thing I do in the other cells where I search for my search term within the workday. However, i've just used the substitute function you showed me to search for either 2660_x (item level) or just 2660 (project level) and return "x" based on that. I appreciate the help

**Glenn Kennedy** · 02-01-2021, 03:54 AM

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

**Tbenz** · 02-01-2021, 08:59 AM

Thanks,

Didnt know how to do that before. Appreciate it.

Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

LinkBack

Thread Tools

Rate This Thread

Display

Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Re: Scheduling: Always Evaluate a column range for Len>0 but with a changing column range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert range to table without changing column width / formatting

Inserting new column into chart data range and range keeps changing.

Copying a changing range from another workbook and pasting it after last column

Named range that returns range in column A based on criteria looking at column B

How to evaluate a data range for max values and list all corresponding column headings

[SOLVED] Cond. formatting a column/Range, highlight cell in column/range if equal to specific text

[SOLVED] changing the color on range in a column when text is in a certain row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions