Search results

Joe, Welcome to the forums! I assume that you want a formula you can paste in your third column, but I'm not sure that I understand exactly what output you expect to get in that column... Perhaps you can expand your example and provide the desired output? Then, we can help compose a formula...

Jaimee, I handled your request similarly to Tomas (with sumproduct). But, if you are compiling lots of this data, it will pay dividends for you to use pivot tables instead of formulas. Excel's pivot tables are pretty powerful. See attached.

Kate, Regarding the Employee Drop-down: You suggested an employee drop-down like the Chandoo Vacation Tracker template, which requires a little creativity. In Chandoo's original template, the employees all fit on one screen, so the dropdown can highlight the selected employee without...

You can use a formula like this (see attached) to extract the first 3 characters in a string: =LEFT(C2,3) If the numerals vary in length (sometimes more or less than 3 characters), but are always followed by a "space", you can use something like this: =LEFT(C2,FIND(" ",C2)) There is always...

I've put together a basic starting point (attached) -- for your feedback, based on the model you referenced...but I just read your most recent post, and I'll have to give that some more thought. Take a look at the dashboard in the attached file and let me know if it's heading the direction you...

Welcome to the forum! I'm taking a look at the files you provided, and I have a question about the Performance Review Dates...There are dates in columns F through P -- which of these dates do you want to include on the dashboard? All of them? Thanks.

Hi - welcome to the forums! Do you want to keep the red text format on the numbers in the combined cell? If you just want to combine the contents without the format, you can do this simply -- assuming your source data is in Columns A and B. In Column C (probably cell C2), enter either of the...

Welcome to the forums; I hope you find the help you're looking for. I looked at the attachments you provided, and I would need some clarification before I could offer any suggestions: The durations in your sample spreadsheet don't precisely match the durations in the text file. How exactly do...

As I reviewed this thread, I realized that I overcomplicated the solution. It should have been as easy as changing this: (in cell E10, the original array formula) =INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF($E$9:$E$9,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1)) to this (see...

Prashantvk, I was intrigued by your problem; and I found a few helpful resources. Resource 1. There is a formula-based solution to this type of problem discussed at the following link: http://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/ It's author, @XOR LX, is...

VvM, Welcome to the forums! The problem in your formula in in the COUNTIF range (bold below) =INDEX(Item, SMALL(IF(($C$10=Category)*(COUNTIF($E$9:E10,Item)=0), ROW(Category)-MIN(ROW(Category))+1, ""), 1)) Try this instead -- paste in cell E10 and drag horizontal to fill. =INDEX(Item...

Syed, Here's a suggestion: You'll have to adjust the M3 references in the following formula to reflect the location where you are pasting the formula =OFFSET($A$2,INT((ROWS($M$3:M3)-1)/2),MOD(ROWS($M$3:M3)-1,2)) See attached.

Chung -- I'm still working on this, and I've got it calculating the subtotals as you wanted. See attached. The "x" in H1:H19 determines which categories are included in the totals; try changing or deleting some of them; you'll see what I mean... What do you think?

Chung, In attached file, note that cell A15 has a dropdown list (populated from on G1:G19) which allows you to selected a specific category you want to see subtotals for. The formula is admittedly cumbersome, but by using some of the data manipulation tools such as dropdown boxes, we can avoid...

My initial observation is that the formula I originally proposed is based directly on the "All Product Groups" line of the detailed report -- and it assumes that each location includes this summary line. Your new request will actually require us to: 1. Test for the occurrence of a specific...

Prakash, Without knowing the specifics of your application, I would suggest that you don't need a formula or VBA. Instead, I would do it this way: Before making a manual color change, hold down CTRL and select all the sheets (at the bottom of the window) that you want the color change to be...

I've composed a formula similar to Luke's (see attached file): =INDEX($L$3:$N$33,SUMPRODUCT(($G$3:$G$33=A3)*($H$3:$H$33<=B3)*($I$3:$I$33>=B3)*($J$3:$J$33<=C3)*($K$3:$K$33>=C3)*ROW($L$3:$N$33))-2,MATCH(D3,{0,10001,25001})) Note: In the third row of sample data you provided, both Luke and I are...

Paste this in X1: =IF(A1-B1<=C1,C1,A1-B1) (see attached) Does that solve it?

Nabeel, Like so? (attached).

Defyrider, Welcome to the forum. Somendra, Just out of curiosity, is there a technical difference between: [@[Training Date]]="" and ISBLANK([@[Training Date]])

Welcome to the forums! Try this formula: =HLOOKUP(K7,INDIRECT(A2&"Tables!C6:O9"),IF(K8=INDIRECT(A2&"Tables!B7"),2,IF(K8=INDIRECT(A2&"Tables!B8"),3,4))) Where all the A2 references in bold represent the target cell where you will simply enter M or F to toggle between the data sources.

Brian, Welcome to the forum! See attached file; using SUMPRODUCT (Narayan beat me to it, above) ...Does this accomplish what you are wanting? It's not in the OFFSET format you initially proposed.

Ronak, I'm not a statistician - forgive me if I've misunderstood your post. Are you asking how to normalize the data from 0 to 3 and then translate it up the scale by a value of 1? I've tried to follow the model in the file you provided. See attached.

Ajeet, Please provide 1-2 lines of an example output you want to see in Data 3.

A few issues: First, I assumed that for every entry, there would be a non-blank value in column C; so I set the loop to run a fixed number of times based on the count of non-blank cells in column C: TotalEntries = WorksheetFunction.CountA(Worksheets("Incomplete").Range("C:C")) If my...