# Few "problems" in EXCEL

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Iggzik

Hello everyone, i have some questions/problems for u kind people to help me with, thanks in advance..

Im trying to do one "big" stat / analysator of NHL in excel. So far im doin ~90% of formulas with IF which is prolly best function, but it cant do everything x(

in 1st worksheet i have "DB" which contains ~800 rows (each row so far contains 10 values) with results from NHL they r written there as it goes so by games/day and here starts problems o

1. vlookup returns only 1st found value -> is it possible with some functions to get next values, etc ?
2. i want to do a table from it, is it possible to do some easy "quick" way?
i can do it through many ifs etc but i will be soon lost in all that formulas x( and my head already hurts from all that thinking how to do everything with one function o)
i managed so far to do from that "DB" worksheets for every single team which contains all games they play so i have now 35 worskheets and to do a table from all that worksheets looks to me silly, but i have no idea how to do it in one sheet x(
3. how to make macro or formula to do this -> i want to pick a team (write to cell f.e. DET) and it will go to worsheet "DET" and copy from that 2 arrays and paste it to a place in actuals worksheet. (key thing is that picking from 30 worksheets by name)
4. im not sure bout this since its some years since i worked with excel, but i think it was possible to do formula that will put results in other cells f.e. IF(K30>1;S6=1;T6=1) but this way it doesnt work x(
Thats so far everything, thanks for your time, have a nice day

#### etaf

Wayne
Moderator
this may be done with sumproduct and arrays - but its difficult to fully understand the need
anyway of submitting some dummy information on a spreadsheet and an example of what you would like the end result to look like

remember this is a public forum, so only dummy data you dont mind the world to see

#### Iggzik

OK, so i attached it and im gonna xplain a bit so u guys had idea what is it for and what i want

So far it is "alpha" version or 0.0000002

Key sheet is DB rest r pretty much formulas and stats already from it,, mostly noobly done so anything u can make better feel free to tell

Sheets Table 01 & standings r pretty much final stats from other sheets. Well but if u wanna laugh a bit Standings <- Table 01 <- ANA <- 00xyz00 <- DB <-- thats pretty much how i did it myself (NOOB)

00xyz00 sheet has on right pretty much pasted whole data from DB where i through ifs took out numbers (row) which reflects rows contained desired key word and that numbers i use in rest sheets ANA BUF ... and with vlookup taking out desired data.

Calculator sheet final (it still tooo far from it) goal is to automaticly through all formulas etc to give me numbers "C3" "C7" which will represent who has a little better chance to win that next game
When i enter key words to B10 & K10 and run macro testcopypaste it fills data lower on page with respective teams so i have stats to work with there.

ANA sheet is so far key one from rest buf, bos, col .... since its still work in progress i will do everything in ANA and when ill feel its "done" i copy paste it through all rest and change only that "row" number and it will calculate everything itself.

OK. So what i need help with right now:

1. in that calulator sheet i use macro testcopypaste to take out arrays from respective teams which i enter into B10 & K10 .. can it be dome with formulas so i dont need macro for it,, or even macro can be done better i think so it wont go to sheet and back but only copy/paste that data

2. in ANA sheet i noobly did today through 8 (nested) ifs formula which gives me W L OW OL from given results of a game. it needs to be simplified

3. For sheet Table 01 Table 01 <- ANA <- 00xyz00 <- DB <-- thats pretty much how i did it myself (NOOB) if it can be done with a formula to do that work it does but to take it right from DB data

4.
i think it contains so far maybe 1/10 of final data and now when i enter new results into DB sheets it takes a lil more time to calculate everything so i need to make it simpler

.. functions i use or im familiar with r IF / Vlookup / countif / transpose (found out yesterday) so as u can see im still noob here

Thanks for any help, Cheers & Have a nice day & please dont kill me

#### Iggzik

why it doesnt attach my file? i have it in "attachments" uploaded but its not on web x(

ok its here now dunno why not before but its there now so everything fine =))

#### Attachments

• 95.2 KB Views: 17
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On