Search results

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...

Sweet! I came up with a Pivot Table similar to Luke. I'm becoming an Excel Ewok!

Luke...that's awesome! Thanks man! Love it!!! Great idea on the Transpose too. Thanks man!!!

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...

Ah...I see what you're talking about. Man! I need more sleep. I was looking in the actual cells. Ha! Thanks again bud!!

Hi Luke- Ugh..I'm sorry. I'm not seeing it. I"m in 2010. What I'm trying to do is eliminate the counts of "1" that roll up to the total. I just want to see the total. Thanks man G

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...

Hi Narayank991 & SirJB7 NarayanK's formula worked. Correct SirJB7, the resource withIN the project needed to start over at 1. No problem. I will try to do a better job of explaining the issue next time. Regardless, I sincerely appreciate the help sir! 3G

Hi SirJB7! THanks for the response. The formula unfortunately stops working after the first change in project number: PNum Name Rnum Mo 2 Joe 1 2012/01 2 Bob 2 2012/01 2 Bob 2 2012/02 2 Bob 2 2012/02 2 Jerry 3 2012/02 3 Doug 1 2012/01 3 Doug 1 2012/02 3 Fred 4 2012/01 3...

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...

Thanks Hui! Seems like I was at least close!

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...

I think what Narayan is saying is that you won't be able to obtain a total value if all of your number values are identified as text. Excel won't process them because it's essentially the same as adding the letter a to the letter f. Makes no sense. If you can at least convert your format over...

Got it to work! Turns out the rankings in the Q column were formatted as text, not numbers. Luke, I thank you my friend. Would love to see Hui break this apart in his formula forensics series!! 3G

Sure. when i paste it, i get a result, but, it's not a valid result. It brings back a value in Column C that is not a ranked item. Then, when I press control shift & enter, it changes to the NUM error.

Thanks Luke! ROWS(K18:K18) is on the dashboard page...the other data is on a page titled "November Issues". Here's what the formula looks like: =IF(ROWS($K$18:K18)>COUNT('November Issues'!Q:Q),"",INDEX('November Issues'!C:C,SMALL(IF(ISNUMBER('November Issues'!$Q$2:$Q$18),ROW('November...

Sorry Fred...the formula got messed up in my pasting... =ROWS($K$18:$K18)&". "&IF(ISNUMBER(Sheet3!Q2:Q18),INDEX(Sheet3!C2:C18,MATCH(K18,Sheet3!Q2:Q18,0))) Thanks

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...

To use text in a SUMIF you have to do is put the name (or text)in quotes =SUMIF(Name,"Bob",Commissions)*. Be sure that the cell with the name is formatted for text so it can read it. Also, you might need to TRIM the cells to make sure you don't miss any (i.e. Bob has a space after his name)...

Hey this is a cool trick!

Hmm! Lemme try that...

As expected, they're all blocked. Luke- I have a data chart in B9:V20 B9:F20 is just information (i.e. department name/etc.) G9:V9 is headings Q1 2011, Q2 2011, etc through Q4 2014 G10:V20 are the columns under the dates (G9:V9) I have 4 milestones: Design, Build, Test, Deliver. I have 4...

Let me see if I can try to upload what I've got (I did it manually for the first go aroudn). We have some issues here at work with blocking file sharing sites...

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...

Thanks Fred! Getting closer. It's stacking the 1 series on top of the other in August. I'd like to fit it in between 8/1 & 9/1, but again, the scale gets distorted once I add the series, and, it defaults to days