How are the column C cells formatted? Manually or conditional? If conditional you can pretty much use the same conditions, just rework them to check the neighboring cells. You're going to need to use the formula option, which can do everything the other condition options can do, you just need to write it out in terms of a formula. If manually you can still do the same thing, you just need to start from scratch.

Looks like we're working on similar things today:



(though I have Man Utd's fixture as easiest, followed by City and then JUST Leicester, but Leicester's is based on my estimate of Hull instead of a calculation)

thanks for the responses.

C is formatted conditionally on a colour scale. I've decided to use a workaround (basing the B colour on the value in C for each individual cell) which dilutes the information but conveys the gist of what I'm trying to visualise so I'm happy with it.
I'm trying a new method of applying variable factors of home advantage depending on each team's actual home form. So Everton have close to zero edge from playing at home, whereas the likes of Man City and Arsenal get a massive boost. Though Man City will be the shortest priced favourites in any book on the planet for week 1, so your methodology must be pretty interesting!

Anyway, once again thanks for the replies! I really ought to sit down and look into the more advanced stuff like pivot tables at some point.

Oh, heh, hadn't turned on that home/away part, I was just going on pure team strength.

I think pivot tables are almost always a bad idea. Better to make your own as you have more control. It's only when you have a ton of data that they're good, and I tend to think it's better to not be using Excel at that point.

Probably a simple/stupid question--I need to print a spreadsheet for work today but I cannot seem to get all the words in every cell to appear. It seems the first few lines in each cell get cut off. Can someone tell me how to fix this? It would be much appreciated if I could get an answer in the next few hours. Thanks!

Turn off text wrapping?

You need to turn on text wrapping or make sure the cell width is large enough to accommodate the word length if there are cells near it that also having contents

The following formula will find the row where the contents of C12 are last mentioned in B1 to B11: =LOOKUP(2,1/(B1:B11=C12),ROW(B1:B11)). This tells me the most recent time that the away team in C12 played at home.



But I want to dynamically adjust the B1:B11 range depending on Cx. C12 is just an example. I can make that range by putting this in J22: =CONCATENATE("B1:B", ROW(C12)-1), which gives "B1:B11" as the output.

Then I can do: =LOOKUP(2,1/(INDIRECT(J22)=C12),ROW(INDIRECT(J22)))

That works fine. But if I put the CONCATENATE directly into the INDIRECT like this: =LOOKUP(2,1/(INDIRECT(CONCATENATE("B1:B", ROW(C12)-1))=C12),ROW(INDIRECT(CONCATENATE("B1:B", ROW(C12)-1))))

Then I get an error code (#N/A). Anyone know why?

You really need to start using INDEX/MATCH. Will have a look and see if I can get this sorted.

I couldnt get yours to work, this is what I have.
This gets me 9 as well and should be fairly flexible input wise:

Thanks, and although that does work for that sample, it doesn't over larger ones. It finds a match of what appears to be its own choice, rather than the last match.

That's why I was using LOOKUP, it was returning the last one in the list. Though I agree with you that I shouldn't use LOOKUP most of the time.

The reason that I need the last one in the list is that I need the most recent Elo rating, and the list is about 6k entries long at the moment. The idea is that after each game I calculate the new Elo, and I can go to any of the old games and find the start and end Elo. It's fairly doable if you do lots of manual entry, but that rather defeats the purpose of me learning more Excel via fantasy football. That's why I don't just have a column that computes what's in J22 for each row, which would be perfectly doable. I was quite surprised this route just didn't work though.

Example of how I can do it with columns (not filled in most the rest of the sheet yet):



Incidentally, let me know if you're in London again Spurious.

I spent too much time on this but I learned two things:
- INDEX MATCH returns the first entry and LOOKUP returns the last value, didn't know that
- Your problem is the fact that the INDIRECT converts to INDIRECT({"B1:B11"}) which is an incorrect evaluation. The problem is most likely related to ROW but I'm not positive here. I honestly don't think that there is a way around it.

Will let you know about London. Might be in the not so distant future, has been a while since I've been to London for more than one night.

I think this function works for doing what you want to do:

=LARGE(IF(B$1:B11=C12,ROW(B$1:B11),0),1)

Thanks for investigating. I shall live with the extra column. It's kind of fun how you can come across so many new things in Excel that I wouldn't see at work, by just doing a hobby-project.

Good good.

Thanks. It looks to me like it should do the right kind of thing, but it just gives me #VALUE! errors.

You would need to enter it as an array formula SHIFT+CTRL+ENTER.

I have column from 17-31 that i want to solve. Problem is too many code, although it is just simple to write it.

https://************/image/qxhjvkhjj/
=COUNTIF(S7:AG7,"P")*1+COUNTIF(S7:AG7,"T")*10+IF(S 7="T",COUNTA(S7:S25),0)+IF(T7="T",COUNTA(T7:T25),0 )

column 17 is S7, 18 is T7, 19 is U7, 20 is V7, and so on . . .


How do i copy the formulas in S7 that contain 7-25 rows range and Make it automatically change T7, U7, V7, and so on

IF(S7="T",COUNTA(S7:S25),0) <---- copy
IF(T7="T",COUNTA(T7:T25),0) <---- paste
IF(U7="T",COUNTA(U7:U25),0) <---- paste
IF(V7="T",COUNTA(V7:V25),0) <---- paste
IF(W7="T",COUNTA(W7:W25),0) <--- paste
IF(X7="T",COUNTA(X7:X25),0) <--- paste

Weird (because I don't know how to phase this exactly) Match function question. Match is of the form match(cell,array,matchtype). If the array is in another tab (say in "tab2" and you want the first column) it looks like match(a2,'tab2'!a:a,0). Is there a way to make the tab2 part a variable.

Like if I'm working in tab1 and enter the tab name I want in a cell (say cell a1). Match(a2,'&a1&'!a:a,0) doesn't work, but is there a way to get the value of a1 in there. So I can just type a different name in there to reference a different tab?

I think you want to use the INDIRECT function.

Would there be any way of interpreting a cell that contains a poker flop and getting some kind of output to describe the flop?

For example, the flop cell contains "As Jc Js", and I'd want an output (or 3 separate outputs) for "A high", "Paired", "Flushdraw".

There are definitely ways to do it, but it sounds quite complicated.

I think my approach would be to have separate columns (Card1suit, Card1rank, Card2suit, Card2rank, etc.) and additional columns with IF statements to check if two or more of the ranks/ suits are the same.

In VBA you could write an algorithm that does it. The major part is writing all the definitions.

Yeah, one way or the other you just have to write functions to interpret the flop. It's not a small amount of work. But no, Excel does not have built in poker flop interpretation functions.

I'd use concatenate, mid(...,...,1) with if statements probably. Like
Concatenate(if(or(mid(a1,2,1)=mid(a1,5,1),mid(a1,2 ,1)=mid(a1,8,1),mid(a1,5,1)=mid(a1,8,1)),"flush draw","no flush draw"), similar statements for evaluating pairs etc...) So ultimately it spits out "no flush draw, pair, straight draw."

Unrelated, I ended up writing a macro for my problem. Indirect didn't help unfortunately.

I'm trying to collate golf stats for each tournament, I've managed to automate most of it using macros, but still need to copy paste a table like the below repeatedly for each player. Does anyone know how I can get this into excel using macros, the data Web page thing isn't giving me useful data.

http://www.pgatour.com/content/pgato...corecards/r027

Yeah for the poker data you could use left right mid etc to separate the data into 6 columns and then count number of suits and if the flop is paired etc. Then produce a table of the flop types you want to show and use index match and if statements to classify. Shouldn't be too hard