1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Few "problems" in EXCEL

Discussion in 'Business Applications' started by Iggzik, Feb 10, 2013.

Thread Status:
Not open for further replies.
  1. Iggzik

    Iggzik Thread Starter

    Feb 10, 2013
    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
  2. etaf

    etaf Moderator

    Oct 2, 2003
    First Name:
    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
  3. Iggzik

    Iggzik Thread Starter

    Feb 10, 2013
    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 :rolleyes:

    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 :D

    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 :confused:

    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 :confused:

    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 :confused:

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

    Thanks for any help, Cheers & Have a nice day & please dont kill me :D
  4. Iggzik

    Iggzik Thread Starter

    Feb 10, 2013
    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 =))

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1089013

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice