+ Reply to Thread
Results 1 to 31 of 31

Very Challenging Multiple Cell Reference Formula

  1. #1
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Very Challenging Multiple Cell Reference Formula

    Hello All,

    This is a difficult problem to articulate but here goes.....I need to create a formula that can upon entering a value in 1 cell must find a matching value within 3 levels of sorting: GENDER (2 Choices), AGE GROUP (6 Choices) and EXERCISE NAME (Numerous Choices). Then it must auto-populate values in a 65 cell array (5 columns x 13 rows) with column labels.... LEVEL, DESIGNATION, COLOR, GRADE, SCALE and 13 levels (rows). So the trigger value that when entered causing all the other associations to respond is the number of reps performed for an exercise.

    Gender
    Man

    Age Group
    50 - 59

    Exercise Name Reps Level Designation Color Grade Scale
    Push Ups 25 9 Intermediate Blue B- 81%


    Thank you,

    Patrick

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Very Challenging Multiple Cell Reference Formula

    Do you have a setup of your array with sample data? The first part of your question might be accomplished using INDEX/MATCH, but without knowing how your data is setup, it is hard to say from there.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Very Challenging Multiple Cell Reference Formula

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  4. #4
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Hi mchahobt,

    Thank you for taking time to help! This didn't cut and paste very cleanly but the array is below....

    Exercise Level Designation Color Grade Scale Reps Height
    Men Men Men Men Men Men Women Women Women Women Women Women
    Side Step Up 20 - 29 30 - 39 40 - 49 50 - 59 60 - 69 70 - 79 20 - 29 30 - 39 40 - 49 50 - 59 60 - 69 70 - 79
    A 1 Legend Gold A+ 98% 3 30 28 26 24 22 20 24 22 20 18 16 14
    B 2 Elite Silver A 95% 3 29 27 25 23 21 19 23 21 19 17 15 13
    C 3 Master Bronze A- 91% 3 28 26 24 22 20 18 22 20 18 16 14 12
    D 4 Expert Purple B+ 88% 3 27 25 23 21 19 17 21 19 17 15 13 11
    E 5 Advanced Red B 85% 3 26 24 22 20 18 16 20 18 16 14 12 10
    F 6 Intermediate Blue B- 81% 3 25 23 21 19 17 15 19 17 15 13 11 9
    G 7 Intermediate Green C+ 78% 3 24 22 20 18 16 14 18 16 14 12 10 8
    H 8 Intermediate Brown C 75% 3 23 21 19 17 15 13 17 15 13 11 9 7
    I 9 Intermediate Orange C- 71% 3 22 20 18 16 14 12 16 14 12 10 8 6
    J 10 Developmental Yellow D+ 68% 3 21 19 17 15 13 11 15 13 11 9 7 5
    K 11 Developmental Black D 65% 3 20 18 16 14 12 10 14 12 10 8 6 4
    L 12 Developmental Gray D- 61% 3 19 17 15 13 11 9 13 11 9 7 5 3
    M 13 Developmental White F 58% 3 18 16 14 12 10 8 12 10 8 6 4 2

  5. #5
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Very Challenging Multiple Cell Reference Formula

    EverClear,

    As FDibbins said earlier, this would definitely be easier to work with if in a sample workbook. Since you already attached some data above, can you insert it into a workbook, format it correctly the way you'd like, then upload it by Going Advanced --> Manage Attachments?

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    Please attach a workbook as preiously asked. Your copy and paste effort cannot be unscrambled sufficiently to make much sense in some areas.

    An expected result should be included.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    mcmahobt, mcfibbins, newdoverman,

    File is attached.

    Sheet 1 has the destination cells where the data will be sent to

    Sheet 2 has the data array

    Thank you for your ocntinued assistance!

    Patrick
    Attached Files Attached Files

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    What does 3b mean? Is it a value that belongs somewhere on Sheet2?

  9. #9
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    No it's just a sequence code for designating that this is the 3rd exercise in circuit b with no precedents or dependents with any formulas.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    I have what I think is a solution basis. It uses two helper columns that determine the start and end of a range on sheet2. The depth of the range to be calculated is determined in part by Sheet1!K9 and the main formula. I expect that your data will grow on Sheet2 so made the worksheet handle approximately 500 rows.
    Attached Files Attached Files
    Last edited by newdoverman; 12-18-2014 at 07:58 PM.

  11. #11
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Smile Re: Very Challenging Multiple Cell Reference Formula

    newdoverman,

    Wow what a complicated equation! I would have never figured that one out. I was not aware of the technique using helper columns! I will attempt to replicate this with other exercises but none are ready so need to build out the number of reps / weight for each and that will take quite some time.

    Just a few questions to further my understanding of the syntax:

    Why are 2 of the formula's (cells E1 & J9) on Sheet 1 not hidden from view?

    Why does the formula in J9 on Sheet 1 have a bracket in front of the = sign?

    Why is the word "Joe" used in E1 cell formula?

    Just Brilliant and a Million Thanks!!!

    Patrick

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    You have gotten hold of a file that I didn't intend to upload. I frequently use formulae for testing before I get into the main formula creation. I will upload a "clean" file. E1 can be deleted. Don't delete J9 or K9. They are vital for the solution.

    Here is a cleaned up file:
    Attached Files Attached Files

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    The word "Joe" was there in my testing of an idea....it was a filler text that wouldn't be confused with anything that belongs on the worksheet. This should have been deleted before uploading....sorry about that.

    Don't delete J9, K9 or anything on Sheet3.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Very Challenging Multiple Cell Reference Formula

    I will answer your questions as best I can, then give you my alternative.

    I dont see anything in E1 on sheet1, but the formulas in J9:K9 are being used as cell refs for all the other formulas
    The Braces around the formulas in J9:K9 tell excel this is an ARRAY formula - ARRAY's use ranges where a single cell ref - or an array when a single range - is expected.

    Again, I see no entry in E1 in any of teh sheets there?

    OK, my suggestion (and please note, there are many ways to arrive at complex answers like this - all experts have their favorite approaches, NDM's suggestion is really good)

    In Sheet1...
    D9=MATCH(C9,OFFSET(Sheet2!$G$5,1,MATCH($B$1,Sheet2!$H$4:$T$4,0)+MATCH($B$2,Sheet2!$H$5:$M$5,0)-1,COUNT(Sheet2!$G:$G),1),0)
    E9=INDEX(Sheet2!C$6:C$18,Sheet1!$D9)
    copy E9 across as needed.

    These are all regular formulas, and are entered in teh regular way

  15. #15
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    FDibbins & newdoverman,

    Yeah I couldn't figure out how that one formula was connected to this! Both formula's worked exceptionally well. I aspire to be at both your levels someday....in my dreams!

    Patrick

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Very Challenging Multiple Cell Reference Formula

    Happy to help, thanks for the kind words and the feedback

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    Glad to be of assistance. Thanks for the kind words and good luck with your project.

  18. #18
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Hi FDibbins,

    In trying to integrate your formula from the sample workbook into my actual workbook, I'm getting a #N/A error:

    =MATCH(C93,OFFSET('Exercise-Progressions'!$G$12,1,MATCH($F$2,'Exercise-Progressions'!$H$6:$T$6,0)+MATCH($G$2,'Exercise-Progressions'!$H$7:$M$7,0)-1,COUNT('Exercise-Progressions'!$G:$G),1),0)

    I changed sheet names and cell coordinates to mirror the actual workbook but it still won't calculate?

    Also, if the gender changes to WOMEN, does the cell range for AGE GROUPS have to be extended from H7:M7 to H7:T7 or does another function have to be added?

    Thank you,

    Patrick

  19. #19
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    In the second MATCH change the $M$7 to $T$7

    If that doesn't fix the problem, upload your actual workbook so that the problem can be rectified.

  20. #20
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    That did not calculate....still #N/A error. Here's the actual workbook Workout-Session-Template-Debra-Steffen-2.xls

  21. #21
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    This worksheet was quite different from the sample. I think that I have made the corrections necessary. The formulae are in the yellow shaded row. The main problem was that you had identified the wrong starting point for the OFFSET and an incorrect range was also entered.

    I notice that you have other exercises on the left but no data for them. The formulae will have to be adjusted to the locations of the data for those exercises and the references will be very different so be careful when making the adjustments and notice carefully what cells are being addressed in the exercise that now works.
    Last edited by newdoverman; 12-19-2014 at 03:33 PM.

  22. #22
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Doverman,

    Thanks for catching my oversight. Sometimes you stare at the problem and you just can't see the forest for the trees!

    I was curious what the significance of using cell....

    $G$7 in the formula on the exercise progressions sheet which is blank?

    $D93 from the exercise progressions sheet is also in a multiple formulas and is blank (Was this to allow space to copy the formula down the page)?

    Do I just copy the formula up or down to link up with the other exercises as it looks like all the static cell coordinates are protected and the only cell that is not is for entering the number of reps?

    Thanks again,

    Patrick

  23. #23
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    $G$7 in Exercise-Progressions was probably chosen because it is in a place that in all likelihood will not change. It could have been somewhere else but the formula arguments would have to be adjusted to take a different position into consideration. $G$7 is the starting cell for the OFFSET function. FDibbins had a plan and it started at that position.

    $D93 is on the Circuit-Builder worksheet and is referenced in 4 cells from E93 to H93. This is necessary to return a value that is appropriate to the value in C93 that has been calculated in D93.

    "Do I just copy the formula up or down to link up with the other exercises as it looks like all the static cell coordinates are protected and the only cell that is not is for entering the number of reps?"

    Now that all depends upon how you enter the data for the other exercises. If the values are such that they would repeat for different exercises within an age group then there would be a problem. Without really studying the situation, the most reliable method would be, I think, to re-create the formulae with the cell addresses adjusted for each exercise range. With a real example of what you are going to do, a better answer could be given.

  24. #24
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Appreciate the clarifications! I see that you used various combinations of 1, 0 and -1 in the formula in D93 (LEVEL Column) in the Circuit Builder sheet. If the REPS are not an exact match in the data tables, how do I adjust the formula to accommodate that?

    Patrick

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Very Challenging Multiple Cell Reference Formula

    OK, not sure if NDM has given you what you need, but based on the forumla you adjused, you had the wrong starting point for the OFFSET. It should have been G7, not G12

    =MATCH(C93,OFFSET('Exercise-Progressions'!$G$7,1,MATCH($F$2,'Exercise-Progressions'!$H$6:$T$6,0)+MATCH($G$2,'Exercise-Progressions'!$H$7:$T$7,0)-1,COUNT('Exercise-Progressions'!$G:$G),1),0)

    As far as needing to change the range for Men vs women, that is not needed. They both have identical headings, so "3 columns in" for men (40-49) is the same as "3 columns in" for women (40-49). That value gets added to either to either 1 (for men) or 8 (for women)

    edit: NDM was spot-on there...
    $G$7 in Exercise-Progressions was probably chosen because it is in a place that in all likelihood will not change
    FDibbins had a plan and it started at that position.
    I often dont use the top-left corner of a range like that, rather, I use the top-left corner of the table (above the 1st row-heading, and left of teh 1st column-heading). That way, if the table is changed (added to or taken away from), that starting point shouldnt be affected
    Last edited by FDibbins; 12-19-2014 at 07:24 PM.

  26. #26
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    He did and thank you for following up and adding further insight to some of the other items. Just waiting to hear back on another formula tweak that has me stumped?

  27. #27
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    Quote Originally Posted by EverClever View Post
    If the REPS are not an exact match in the data tables, how do I adjust the formula to accommodate that?

    Patrick
    The values in the data tables are in increments of 1 and are in order for each age group. What kind of value, if not out of range, are you referring to? Can there be fractional values?

  28. #28
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Not fractional values. But if one enters a value that is either above or below the range because the person scored either very poorly or very well, it will not use the closest value and thus will not register in any of the columns which renders it's inclusion in an overall score impossible.

  29. #29
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    Also forget to mention when there are weightlifting exercises and for example, the 13 achievement levels are in 20 lbs. increments, there will be exercisers whose best effort will be somewhere in between these increments so the formula should select the closest level below. For example, Level 1 is 100 lbs. and Level 2 is 120 lbs. and the person lifts 110 lbs., the formula should select Level 1.

  30. #30
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Very Challenging Multiple Cell Reference Formula

    What you have given is a very good reason to have a separated table for each exercise. This would be a lot of work but it would be far more functional than trying to make "one size fits all" kind of thing. I think that you have reached the point where a detailed example of what the real finished product will be should be given with all the exercises. Otherwise this will be a never ending bit of adding this or that sometimes having to backtrack past work in order to get to the desired outcome.

  31. #31
    Forum Contributor
    Join Date
    12-16-2014
    Location
    Riverside, IA
    MS-Off Ver
    2010
    Posts
    156

    Re: Very Challenging Multiple Cell Reference Formula

    I had already planned on having a unique table for every exercise due to the extreme range inherent in peoples capabilities but still contained within a standardizedformula template that would automatically adapt to the size of the increments between levels. A parallel comparison in my limited understanding of FUNCTIONS is for example,when using VLOOKUP, one can use FALSE for an exact match or TRUE for a close match.

+ 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. [SOLVED] Formula to reference an multiple cells to one cell.
    By RizzlaXD in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 12-09-2014, 12:46 AM
  2. formula to combine multiple cells into single cell where reference is same
    By rexer231 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-12-2014, 11:44 AM
  3. [SOLVED] Challenging complex formula- unique count for multiple criteria on several levels
    By joannelittell in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-30-2013, 09:52 PM
  4. Macro to Segregate the data based on Multiple Criteria's in Multiple Columns -Challenging!
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-08-2012, 07:31 AM
  5. Replies: 3
    Last Post: 01-28-2011, 03:19 AM

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