+ Reply to Thread
Results 1 to 18 of 18

=IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

  1. #1
    Registered User
    Join Date
    04-04-2020
    Location
    Newcastle,Australia
    MS-Off Ver
    16.33
    Posts
    4

    =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Hi,

    Im reasonably new to excel.
    Im hoping someone can show me a formula to help me.

    Is there a formula that i can put into cell B26 next to Brad White that if B4 has a value of 1 then it will = B6 on the Sydney points table. If the value of B4 is 2 then it will equal B7 from the Sydney points table. All the way down to a value of 24 that would = B29 from the Sydney Points table.

    I have attached the file as well as the photos.

    Every thing i know i have just learnt from google but Im not sure how to do this.

    Thanks in advance.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by whitebread1990; 04-05-2020 at 12:23 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Help with Formula.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-04-2020
    Location
    Newcastle,Australia
    MS-Off Ver
    16.33
    Posts
    4

    Re: Help with Formula.

    Hi Alan,

    Thanks for your reply.
    Im trying to think of how to describe my issue but am not very trained in excel so Im not exactly sure how to summarise it.
    I think it will be with an =IF formula but other than that im not very sure sorry.

    Ill try to explain it better.

    brad

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    It's not at all clear what you are trying to do here. You need to mock up some results manually where you want to show them. From what you are describing, you need a lookup table, but again, I have no idea really where this is all going, because there are no values in the cells you wish to reference.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    G'day Whitey,

    Welcome to the forum mate,
    Fellow Novocastrian (Hunter Valley) here bud
    I'll take a look in halfa when home!

    Cheers
    Chad

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    No need to wait for Chad to get home. I'm looking at it now, but need the extra information requested in post #4.

  7. #7
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Hey Whitey,

    I'm like you mate, Only learning brother,
    Plenty of geniuses here to help mate

    Please Login or Register  to view this content.
    One of the guru's will go way past that mate!
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    That only deals with the first possible result - the OP has shown in the thread title that there's more to it. It will quickly become unmanageable if you continue down the nested IF route.

    @Whitebread - we really need some more input from you. Please would you respond to post #4?

  9. #9
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    I see your point Ali, Duly noted

    @Whitebread - Maybe if you give the points system/table info, Ali or others will be able to make up something that will reference said system/table

    Good luck mate
    Last edited by AliGW; 04-05-2020 at 03:07 AM. Reason: Please don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    I was beginning to think I was invisible ... By the way, I have asked for clarification in your own thread, as well.

  11. #11
    Registered User
    Join Date
    09-03-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    96

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Quote Originally Posted by AliGW View Post
    I was beginning to think I was invisible ... By the way, I have asked for clarification in your own thread, as well.
    @Ali, My bad, flicking in & out of threads TRYING to source,help,learn lol
    I'll respond to that one shortly

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    I've learnt pretty much everything I know here.

  13. #13
    Registered User
    Join Date
    04-04-2020
    Location
    Newcastle,Australia
    MS-Off Ver
    16.33
    Posts
    4

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Sorry guys i was away from the computer.
    After i started the thread i kept trying to come up with something. Ill attach what i have so far.

    This is for a horse racing tipping comp.

    In the Results tab the boxes have a drop down menu with options 1st, 2nd, 3rd and 4th.
    Depending on the race we will input the results.
    if you choose one of these results then switch to the Points1 tab.
    In the corresponding box it will give points either 10, 5, 3 or 1 depending on the position chosen.
    All the boxes in those two tabs are the saddle number of the horse and the race number and location.

    Up to there is fine.

    In the selections tab i want to be able to choose the horse number in a certain race with different
    people and have the points tally up in a seperate table.
    I hope that makes sense.

    Thanks
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Corona Stakes, eh?

    I think you will need to set up some lookup tables to make this work efficiently. I'll have a look at the new workbook now.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    For starters:

    In B41 (Selections), paste this:

    =IFNA(INDEX(Table411[SR1],MATCH(B4,Points1!$A$6:$A$29,0)),"")

    Drag copy down.

    Now, please give me a worked example of this to clarify what you want:

    In the selections tab i want to be able to choose the horse number in a certain race with different
    people and have the points tally up in a seperate table.
    I hope that makes sense.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    Here's a more streamlined formula for B6 on Points1:

    =IFNA(LOOKUP(Results!B6,{"1st","2nd","3rd","4th"},{10,5,3,1}),"")
    Attached Files Attached Files
    Last edited by AliGW; 04-05-2020 at 05:57 AM.

  17. #17
    Registered User
    Join Date
    04-04-2020
    Location
    Newcastle,Australia
    MS-Off Ver
    16.33
    Posts
    4

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    No need to clarify.
    That is exactly what i was looking for and lets me do everything i need to do.
    Thanks so much, saved me a lot of time trying to figure it out myself (although i don't think i would of ).

    Thanks again.

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: =IF(B4=1,"Points1!B6"),IF(B4=2,"Points1!B7")

    No problem. There are lots more things you could do to automate this further, but maybe that's for another day. Get used to the formulae you've been given today first.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  5. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  6. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  7. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1