Sheet named Season 1
FF.PNG
Sheet named Records
FF2.PNG
Formula in B4 is =LARGE('Season 1'!B2:AB11,1)
,1 changes to ,2 ,3 ,4 etc.
I want to find who scored the point total in column B
=INDEX('Season 1'!A2:A11,MATCH(B4,'Season 1'!Z2:Z11,0))
^Will return the answer but then I've manually done all the work
I want to be able to search the entire array like so but using this formula
=INDEX('Season 1'!A2:A11,MATCH(B4,'Season 1'!A2:AB11,0))
returns #N/A
Hi, welcome to the forum
Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum
Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Fantasy football.xlsx
So on the records tab in column B it finds who in season 1 scored the most points (top 10 actually), in column A I want to find who scored that corresponding value. I know I might run into issues when the value happens more than once, 152 and 138 for example.
I have it in there with =INDEX('Season 1'!A2:A11,MATCH(B4,'Season 1'!Z2:Z11,0)) right now and it pulls the correct data but that involves me manually finding the value, I'd like for it to be able to do it across all columns. I know index+match doesn't do this but hoping someone has an idea.
Try changing your formula in B4 to
=LARGE('Season 1'!$B$2:$AB$11,ROWS($1:1))
and filling down.
With the range set absolute and the second argument of LARGE determined by the convenience of ROWS you will save copying and pasting each repetition of the formula.
Then array enter this formula in A4 and fill down.
=INDEX('Season 1'!$A$2:$A$11,SMALL(IF(B4='Season 1'!$B$2:$AC$11,ROW($B$2:$C$11)-MIN(ROW($B$2:$C$11))+1),COUNTIF(B$4:B4,B4)))
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Repeat for each Season section. (You will need to change the sheet references in each formula.)
Your reworked workbook is attached.
Dave
This worked perfectly, thanks Dave!
You're welcome. Thanks for the feedback and the rep.
So I got the playoff high score to work with: =LARGE('Season 1:Season 3'!$AF$2:$AH$11,ROWS($1:1))Originally Posted by FlameRetired
You're welcome. Thanks for the feedback and the rep.
but using =SMALL('Season 1:Season 3'!$AF$2:$AH$11,ROWS($1:1)) Cell H18 returns 0, at first I thought it was because AF is blank in Season 2 and 3 but if I do =SMALL('Season 2'!$AF$2:$AH$11,ROWS($1:1)) Cell H19 it returns 32 which is correct. Why does LARGE work across multiple sheets but SMALL doesn't? I attached the workbook again
Because in 'Season 1:Season 3'!$AF$2:$AH$11 I count 30 blank cells (not including the merged cells). SMALL is going to have to reach at least ROWS($1:30) before it has exhausted those, and they are all evaluated as 0s. You can demonstrate how this works by temporarily filling all of those blanks with -1s. All of your SMALL formulas will return -1 because it is now the smallest and there are 30 of them.
In addition some of the cells in 'Season 1:Season 3'!$AF$2:$AH$11 are text AND they are in merged cells. Merged cells cause havoc. They are real pain to 'formulate' around. Try to avoid them in your data tables especially. Your columns are also not consistent in addressing or purpose. I wouldn't try working out the rest of these formulas without those remedies. You'll need consistency in order for this to work reliably and predictably.
I was thinking the same thing so then I filled in all those cells in and even changed the formula from Season 3 to Season 2 but your thinking gave me an idea. I wonder if since it's spanning from Season 1:Season 2 or 3 if it's looking for content in AH2-11:infinity basically? since all those cells after week 16 are blank
I don't think so. But I've been considering another idea. Do those merged cell column breaks with 'Playoffs' have to be there? If not I might have a remedy. Otherwise I'll have to try something else.
They don't it just separates the numbers nicely but I even tried =SMALL('Season 1:Season 2'!$AG$2:$AG$11,ROWS($1:1)) to use only the rows consistent across both sheets and still got 0
Last edited by plotting; 09-29-2015 at 12:05 AM.
I'm calling for community help.
Got a report back from one of the community members (quekbc).
Quote Originally Posted by quekbc View Post
Now, here's the funny thing. Try creating the formula by typing and selecting the cells using the mouse. I find that I cannot select Season 1 Season 2 and Season 3 sheets at the same time, i.e. it doesn't allow me to do 'Season 1:Season 3'. I don't know why that is the case. Here's the better part, when I create a copy of all the sheets unto a new workbook, it now works - and the SMALL function works too. Another Excel oddity perhaps.
Expanding on that, I know why it doesn't work now.
You have hidden sheets slotted between Season 1 and Season 2, and hidden sheets slotted between Season 2 and Season 3. These hidden sheets are Season 1 Data, Season 1 Schedule, Season 2 Data, and Season 2 Schedule. So when you're typing in the formula =SMALL('Season 1:Season 2'!$AG$2:$AG$11,ROWS($1:1)), it is looking at the cells of AG2:AG11 of sheets Season 1, Season 1 Data, Season 1 Schedule, Season 2, Season 2 Data, Season 2 Schedule, and Season 3. In the bolded sheets, there are numbers 0 which affect the SMALL formula.
Sure enough moving those sheets helped, thanks guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks