Search results

Hi there- I have a user access form with some data validation that I'd like to have "blank" until a user selects some of the drop downs. However, my current formula is returning the result of the IFERROR condition as nothing has been selected. What I'd like to have happen is if F10 is blank, I...

I've seen most websites suggest the INDIRECT() method when it comes to using dependant validation filters. However, can someone explain why the Offset approach doesn't work for the second drop down to create a dynamic range? Do Volatile functions not play well in this format? Or, does it work...

Hi there- I have some data validation columns (3 total) set up, with the third column dependant on the first two. However, that third column is a dynamic list as a result of the first two selections. The dynamic list is populated by the following formula...

Hello- I'm trying to use Icon Sets to display % actual complete vs expected % complete for a software build. The % complete is purely manually entered, however, the expected % complete is calculated based on a formula where the Elapsed Days of Build is Divided by the Total Days of Build. For...

Hi there- Wondering how I can add additional criteria to an array formula like this: =IFERROR(INDEX($C$2:$C$453,SMALL(IF($M$2:$M$453<TODAY(),ROW($M$2:$M$453)-ROW(!$M$2)+1),ROWS($M$2:$M2))),"") = C+S+E Is it as simple as just adding "AND" to the IF...

Is there a way to "$" a table heading so it doesn't move a column when I drag it? I spend a lot of time fixing the table heading in a formula after I drag it to the right/left, when I want that to stay fixed. Other than manually typing in the cell range, is there another way? perhaps this is a...

I'm having some issues with an array formula, similar to the one Luke did in Formula Forensics 3. Good ole =INDEX(SMALL(IF) Here goes: I've got a list of data that is ranked based on shortest to longest. It is NOT in sort order, but, has a value of 1, 2, 3, 4, or 5 (K28-K32). This sort order...

Hi there, I'm trying to figure out the % of time for a specific interval based on the total length of time for a task. For example, here's my data: These are intervals of time BETWEEN two tasks. I need the metric of % of time for intervals between tasks. Int 1 ||Int 2 ||Int 3 ||Int 4...

Hello- I'm stumped! I have some blank rows I am trying to avoid filling in manually: A| AB| CD| DE| There are ALWAYS 9 rows between values. What I want is all of the blanks populated with the value above it...so: AB AB AB CD CD CD DE DE DE etc.... I tried the Ctrl+enter...

Hello there- I've searched the box and read countless threads, but, still having the same issue w/conditional formatting. I've made a small Gantt inside a dashboard, and want to fill the cells when a date is between the start & end date(s). My formula within the Gantt is great, and...

Hi there- I have a formula that works perfectly fine with normal ranges. I've decided to add dynamic ranges so I don't have to manually update them when I paste the new data into the worksheet. However, since converting them, my formula doesn't work. If I convert one of them to a normal range...

Greetings- Similar question I asked here : http://chandoo.org/forums/topic/sumproduct-question-2 I've got a list of 7 items (A2:A8). On another sheet, I have cells that have multiple combinations of these 7 items. I basically need to count the number of occurences of each item. Data looks...

HI there- I have the following formula: =SUMPRODUCT(([@Status]="Approved")*(([@[System Solution]]=FIND("Software",[@[System Solution]],1)))) It's working, as I'm getting "VALUE" for those that do not have the value "Software" in the System Solution column, and a "0" for the ones that do...

I did a little digging and found a formula to allow a dynamic range, ignoring blanks. I'm wondering if someone can explain the Offset formula: =OFFSET($F$9,0,0,MATCH("*",F10:F16,-1),1) I understand the match piece where the * is a wild card, and, the -1 is a close match...but Offset still...

Hi there! Lots of great threads going on right now. I'm learning a ton!! Of course, I have a problem though. I was given a spreadsheet with multiple numeric entries in one cell like this: 19296 19297 77786 77787 Some have only 2 entries, others have 15. What I'm trying to do is pull...

Greetings...long time no post! Quick Pivot table question...When using the "Count of" in the Values box of a pivot table, is there a way to suppress the "1"s when the fields are expanded to their most basic level? My temp fix is to simply change the text color to match the field, but...

Hello- I have a list of project numbers, resource names, hours and months. What I'd like to do is assign a resource number to each resource (i.e. 1, 2, 3) per project. The column for Resource names has mulitiple entries per name, based on the number of months of the project. For example...

Hi there- I'm trying to create a dynamic named range that will automatically update based on the current date (mainly the month). The list is in a row, not columns. Here's what I have: = OFFSET($D$2,0,1,1,COLUMN(D15)-COLUMN(MONTH(TODAY()) What I want is the range of months to shrink as the...

Hello- I'm thinking an array formula is in order here...but having issues with getting mine to work. Here's my current formula: =ROWS($K$18:$K18)&". "&IF(ISNUMBER(Sheet3!Q2:Q18),INDEX(Sheet3!C2:C18,(Sheet3!Q2:Q18))) What I'm trying to do is produce a list of 6 items on a dashboard page, by...

Greetings I've seen on occasion, Chandoo will put symbols/etc in a table, and, allow them to appear on a graph as added visualization. I'd like to do something similar, but, am not sure how to accomplish it. I have 4 values (1-4), with 4 different colored boxes. I will index/match the values...

Hi there- Have months 1/1-12/1 on my X axis. However, I have 1 data point of 8/16 that I also need to show on the chart. As such, there are 13 data points. When introducing the 8/16 to the chart, it messes up the axis and puts it to days. What I'd prefer is to have all months, and then 8/16...

Hi there- I was practicing Chandoo's circular reference/in-cell chart function lesson whereby you change the iterative calculations & whatnot. Ever since doing that, my Excel has been significantly slower, and, I am now getting this message in the lower right hand corner of Excel showing my...

Hi there I have a project number with multiple lines that has different people at each line. What I'm trying to so is assign a number to each person (i.e. 1, 2, 3, etc). Here's how the data looks: Project Number|Project Name|Named Resource 2|System Remediation|Jack Sprat 2|System...

Hello- I have a list of project Numbers, dates on one page (start & End), and, a spreadsheet of months horizontal with hours broken out in the appropriate months. What Im trying to do is match the Start & End dates with the first & last occurence of the data on the 2nd page to make sure they...

Hi there- I'm struggling with trying to fit a data chart on a page in a 14 point font. There comes a certain point where if I make a cell to tall, or, to wide, it takes the chart and makes it smaller. There is a signficant amount of blank space on the page, and, I want to make the chart legible...