Search results

@Jet Fusion -- I've helped with the first element in your request above: Sheet 2 drop down boxes and matching info in Column D. See attached. Your remaining requests are difficult for me to understand clearly. Do you care to provide a little more sample data, or a little more explanation?

Great! Glad it's helpful! I suggest setting up Named Ranges in the Roles Sheet and the Availability Sheet. They can initially be defined as 56 rows, and updated as needed in the Name Manager. By doing this, your Rota sheet can simply refer to the Named Ranges rather than specific cell...

shiftsailor, I don't think this solution is perfect, by any means, but if you really are just wanting someone to 'point you in the right direction'... I'd suggest multiplying the Role x Availability x Volunteer ID and setting a randomizer to select from them. There's always a chance that...

selling_stuff, I assume the 9-box-grid you are referencing is the one in the November 17 post on the Chandoo blog? https://chandoo.org/wp/9-box-talent-map-template/ I've pulled the sample file from that blog post and inserted an additional filter as you suggested -- using Directors named Mike...

Ufoo, After too long to wait...see attached.

Ufoo... I've got a solution to offer, provided that you can be a little flexible in your requirements: First, I've dumped your data into a single master list, with a Family ID indicator and a binary Adult Indicator. Next, I've used RAND() to sort the master list into a randomized list of gift...

premjeetgupta, There may be better ways to do this than a formula -- so be careful what you wish for... In cell A1, paste this array formula, and execute with Ctrl+Shift+Enter: =INDEX(Sheet4!$A$1:$BC$1000,INT(SMALL(IF(ISERROR(FIND("sh firmware...

xlnovice2000, I've done my best with the sample file you provided. Something like this: =$F2*INDEX(taxtable[Rate],MATCH(G$1,taxtable[Tax],0)) Looks like Column I does not have a rate in your tax table, so it's throwing an error. See attached.

Derek -- Breaking your original post into its constituent parts: ================= Looking first at cell W5. In the sample file you provided, when I pull up the Name Manager to check on the definition of Joint_PL, I see a #REF! error. [It appears to refer to a different Excel file on your...

Per your subject line: Is VLOOKUP the only acceptable solution? How do you plan for the user to select the horses to pass through to the race table? I've assumed that you'll be adding a "selector" column to your source table; and I've also assumed that an array formula is acceptable. See...

Congratulations to @mjazz for the fortitude to read threads more than a year old in search of an answer. @SirJB7 -- Sir, I respectfully take exception to your reply; I appreciate users replying to the original thread so that I (and others) can see that this is a common need among Excel users...

In cell e23: =INDEX('Monthly Summary'!$C$21:$C$32,MATCH($D$16,'Monthly Summary'!$B$21:$B$32)-1) See attached. But it will fail in January each year.

paste in c2: =IF(MOD(COUNTIF($A$2:A2,A2),12)=0,"yes","") and drag to fill. Then, conditional formatting formula will look like this: =c2="yes" See attached.

If you click on the Project Plans tab, you'll see that there are already tables set up for Projects 1-16. If you click on the Formula Ribbon > Name Manager, you'll see that the Named Range 'lstPlans' only includes 10 of the projects...

I'm confused: 86 and 43 are also common values in both cells. Does the position in the sequence have to match as well as the value? Your original post is unclear. This CSE (array) formula will test for common values (but not common position)...

Beautiful, Bosco ! -- Exactly the guidance I needed. Hui, Chihiro: I appreciate your suggestions, but Pivot Tables and Power Query are hard for me to adapt to a format I can easily share with and explain to co-workers. (I always end up getting called to their desk: "How did you do that...

Friends -- Take a look at the attached sample spreadsheet. Given a set of input keyed in the grey boxes, I am looking for a drag-to-fill formula for the orange cells. In the Itemized Output (orange cells), each item repeats as many times as indicated in the corresponding grey box. The final...

Note that in the sample file you provided, you need to update the range in the original formula that bosco_yip provided: =INDEX($B$2:$B$4,MATCH("*"&D4&"*",$A$2:$A$4,0)) Then -- it works as long as the text string in the cell is less than 256 characters (see attached). There may be a 256...

Well -- the array formula below might work... =INDEX(B1:B2,MAX((LEN(A1:A2)<>LEN(SUBSTITUTE(A1:A2,B4,)))*(ROW(A1:A2)))) It would help to have a larger data sample in order to run some checks... See attached.

What about this formula: =MAX(((J11-$H$6)*F11*3.5),0)+IF(J11-$H$4<7,0,MAX(((J11-$H$5)*3.5),50)) Pasted in Q11 and drag to fill.

Using the same basic framework that you already have in your conditional formatting formula: In cell L3: =SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sat")*($N3:$AR3="P")) In cell M3: =SUMPRODUCT((TEXT(DATE(2016,A3,$N$2:$AR$2),"ddd")="Sun")*($N3:$AR3="P")) Drag to fill.

Mike -- a brief explanation of my suggestion: =INDEX(N$2:S$2,,7-(SUMPRODUCT((E4=L$3:L$7)*(D4>=N$3:S$7)*(N$3:S$7<>"")))) Beginning from the end: Test 1: N3:S7<>"" tests of every cell of the lookup array, returning TRUE if the cell is not blank, and FALSE if the cell is blank. The result is...

Using the original file, paste in G4 and copy down to fill: =INDEX(N$2:S$2,,7-(SUMPRODUCT((E4=L$3:L$7)*(D4>=N$3:S$7)*(N$3:S$7<>""))))

For the sake of the cause (and for my own future reference): another array solution with simpler construction than my first proposal. =INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$C$1:$C$100="50 Pack",ROW(Sheet1!$C$1:$C$100)),ROWS(A$2:A2))) Attached.

Amanda, See attached. I've got an array formula something like this: =INDEX(Sheet1!$B$1:$B$100,SMALL((Sheet1!$C$1:$C$100="50 Pack")*ROW(Sheet1!$C$1:$C$100),ROWS(Sheet1!$C$1:$C$100)-COUNTIF(Sheet1!$C$1:$C$100,"50 Pack")+ROW(A1))) I admit to hating the last part of the...