Search results

Joy, Welcome to the forums! You've come to the right place. Chandoo is the first place I go when I have an excel question. As I see it, you've asked for two items: (1) a data table with conditional formatting and (2) a dashboard to summarize that data. I've put together a sample for the...

Shahrukh -- still no file attached?

Jagdev, I think Faseeh has provided what you asked for...His formula works for me -- with one change -- I am using F12 instead of D12 as the lookup value in the MATCH formula. Paste the following formula in Cell G12 and drag down...

Santhos, I propose the following array formula, =SUM(IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),1)),0),IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),2)),0),IFERROR(INDEX(B4:M4,SMALL(IF(B4:M4<>"",COLUMN(B4:M4)-1),3)),0)) Paste in Cell N4, press Ctrl+Shift+Enter to...

Pao, Does the attached do what you're asking?

I do think I see -- but one more question -- when you hid the even rows, you also hid half of the expiration dates in column P. Do you want to use just the expiration dates that are displayed, or do you want to use all the expiration dates (even the hidden ones)?

Jess, If you look at the fix you proposed in the previous post, and change cell D9 to 12%, that row stays yellow (but I think you want it to go to red, right?) My concern is that you may be creating overlapping formatting rules where more than one can be true at the same time -- Luke M taught...

Pao, I observe the first error on the same row as the first blank in Column P. Is this your concern?

Does this do it? =IF(P33-B33<16,(P34-B33)/365,(P33-B33)/365) All best.

Chung, See the attached example, it provides a very simple illustration of the concept that I would suggest for you. However, you know your situation best, so I'll leave it to you to decide whether it makes sense to invest enough to make this happen. You mentioned that your experience with...

Rodrigues, Welcome to the forums. In cell J6, try this formula: =SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="1"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23) And J8: =SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="2"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23) Is this what you're looking for...

Will this do? =IF(AND(COUNTIF(A:A,A2)=5,I2="Retiree"),"Retiree","nope")

Good Life, Welcome to the forums. The SUMPRODUCT formula is what you want. See attached. All best.

Jess, Welcome to the forum. Take a look at the attached file; does it do what you want? I noticed that you are putting an IF() into your conditional formatting rule, which isn't really necessary, because the conditional formatting is by definition applied only when the formula you provide...

Generally, you'll find that it is best to use formulas that don't have to be changed every time you update the spreadsheet...but I tried to fit my solution to the sample file you had provided. The short answer is yes. You can easily replace all the occurrences of April! with May! by...

Helio, I've used this link in the past to do the type of thing you're asking: http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/ I think a variation on this array formula from that site would probably do the trick...

Red, Don't be offended by my saying that you were asking the wrong question. You see, you didn't have a sorting problem, you had a faulty data correlation. In fact, the root problem is that on your Tabulation worksheet, your formulas are written to calculate in the order that the data appears...

Chung, Take a look at the attached. I've created formulas for April 2014 and April 2015 based on the directions you provided in the third paragraph of your original post. An aside: There is some data clean-up needed. The monthly spreadsheets sometimes include or omit the leading space " "...

Still need help? I can give this some attention if you do.

There are a million ways to skin this cat. Let's try this one first: In the Prod Hrs by Month table, use formulas to fill columns A, B, and C. In A2, paste: ='Prod Hrs New'!A2 In B2, paste: ='Prod Hrs New'!B2 In C2, paste: ='Prod Hrs New'!C2 Drag these formulas down to fill the whole table...

Code Red, Welcome to the forums... Pivot Tables were created for exactly this type of application. See attached -- Is this what you want? All best.

Tiago, It looks like we've got bi-weekly periods in the source sheet, and those are being converted to monthly periods in the tabulation sheet. So - - the period 07/12/14 on the source sheet is divided by 14 days (because it is a 14 day period), and then multiplied by 12 days because only 12...

See attached. I've put some test data (SEC,SUP,FE) in column B, and updated the formulas in H6 and K6. You can copy those formulas down as far as you want, and I think you will find "-" in the unused rows. Anything remaining?

I don't know how to put the OR logic into the SUMIFS formula...is this a suitable workaround? =SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"B")+SUMIFS($B$3:$B$27,$C$3:$C$27,"0 to 30 Days",$D$3:$D$27,"E")

Anthony, As I look over the file you uploaded, I don't see the SUP or SEC or FE items that you referenced in your original post. Can you help me interpret what I'm seeing? I understand that you want to build a formula for the Audit Due column that will display "Not Applicable" or Start Date +...