+ Reply to Thread
Results 1 to 40 of 40

Formula must reference same cells after those cells are shuffled [Google Sheets]

  1. #1
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Formula must reference same cells after those cells are shuffled [Google Sheets]

    Hey Everyone. Take a look at this pic (Hope this works).

    Column A is a formula for Columns B:D. When I randomize A:D, my results yield what you see in F:J (Just ignore Column G as I inserted it stricly for aesthetic reasons after the randomization. But what I really want in Column F is what you see in Column L. I want the formulas to point to their original cell contents no matter where they are randomized to. Anyone have any idea how to do this? I've tried $ signs in front of them, I've tried different cell randomization to no avail. Anybody? I sure could use some help on this. I've racked my brain for hours and hours. I don't even know what to search for to ask the right questions. Please help. Thank you.

    Spreadsheet Sample.jpg
    Last edited by 6StringJazzer; 05-12-2022 at 10:15 PM. Reason: Moved from Excel Formulas & Functions

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Can you figure this out?????

    We have a couple of problems here.

    First, your title doesn't tell us anything about your question. That could be the title of any post here. After I figure out what you are actually asking I will help you update your title, since it is your first post.

    Now then. It is not at all clear what logic you are applying to get the "desired" result. What do you mean in this case by "randomization"? What exactly did you do to get to the "Formula After Randomization" version? How are you determining what rows have the formulas in the "Desired" version? How you are determining what those formulas should be?

    We can't tell you how to do something in absence of a description of what to do. It would probably help if you give us the big picture and explain what this data means and what you are trying to do.

    Also I can't see the utility of formulas that all sum blank cells, but let's not worry about that just yet.

    Oh, also, do make the effort to tell us your Office version. Some functions and features in later versions are not available in earlier versions. We don't want to give you a solution that you can't use.

    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. Tell us what you are trying to do, not how you think it should be done.

    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.

    I'll help you once we sort out what your question is.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    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,369

    Re: Can you figure this out?????

    Administrative Note:

    MS-Off Ver: Unspecified
    Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    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.

  4. #4
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Can you figure this out?????

    We have a couple of problems here.

    “...First, your title doesn't tell us anything about your question. That could be the title of any post here. After I figure out what you are actually asking I will help you update your title, since it is your first post…” If I actually knew what question to ask, I would have put it in the title. I explicitly said “...I don't even know what to search for to ask the right questions…” So if you know what question I need to ask, by all means, I’m all ears.

    I have several hundred rows. I gave you nine of them. The data in every three rows goes together (i.e. the value of 1,101, 201 go together, 2,102,202 go together, 3,103,203 go together, and so forth and so on) Those are all in column B. I can’t give you specifics about each of the ITEMS (A through I) as that is proprietary content. Because the entry is taking place by individuals in close proximity to one another, we want to randomize the data and checkpoints in Column D so that no one can look at someone else’s computer adjacent to them, and copy their answers to prevent cheating.

    My humble apologies, as I omitted what lies in column E. That was an oversight. It is a simple formula of E4=if(D4,4,); then E5=if(D5,2,); then E6=if(D6,1,); and then copied and repeats itself for the remaining rows every three rows in the spreadsheet.

    As long as I don’t randomize it, no problem. But when I do, I want the calculation in column F to continue to track and calculate based on 1 and ITEM A, 101 and ITEM B, and, 201 and ITEM C respectively no matter what row they happen to land in after the randomization. For example, if they end up in Rows 19, 237, and 4, respectively, I want it to do the calculations E19=if(D19,4,); E237=if(D237,2,); and E4=if(D4,1,). Then when it does the final calculation in Column F, for these three items, it would be F19=E19+E237+E4. That’s what I’m trying to get it to do. Incidentally, I don’t really care where it does that calculation in Column F as long as it keeps the three together to make the calculation.

    I’m not doing this one in Excel because many who are taking this test don’t have OFFICE. Therefore, I’m deliberately doing this spreadsheet in Google Sheets because everybody has access to it regardless of whether they have Excel or not. I’m not going to make Excel a mandatory requirement for them to take it. So it is independent of Microsoft Office, but I want to be able to import it into OFFICE for those who do have it.

    Feel free to amend the title to better reflect what you think it should be. I don’t know. If I did, I would be happy to amend it.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Can you figure this out?????

    Quote Originally Posted by SpreadSpectrum View Post
    no one can look at someone else’s computer adjacent to them, and copy their answers
    So these are some sort of questions? And people answer them? Is this like multiple choice with three options for each question? (I would have put all three choices in one row, instead of three rows. I think that is part of what is giving you a headache.)

    Quote Originally Posted by SpreadSpectrum View Post
    I’m deliberately doing this spreadsheet in Google Sheets
    Well, we're going to have to move it to another subforum then. This one is strictly for Excel, and the answer in Google could be quite different.

    As to the title, I think you want formulas to continue to reference the same cells even after those cells have been moved around. But the one last thing I would like to know is how are you randomizing it?

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Can you figure this out????? [Google Sheets]

    I think I may have enough to figure this out. I assume your numbers in column B are real, and you do not have more than 300 rows. Put this formula in A4 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is an Excel formula but I think it will work in Google Sheets.
    Last edited by 6StringJazzer; 05-12-2022 at 10:20 PM. Reason: Inequality was going the wrong way, correction in red

  7. #7
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    “...So these are some sort of questions? And people answer them? Is this like multiple choice with three options for each question? (I would have put all three choices in one row, instead of three rows. I think that is part of what is giving you a headache.)...”

    No, they are not multiple choice. You can select none or up to all three, so there are a total of 8 possibilities (think binary…hence 4,2,1, and of course 0) to add up to a range between 0-7.

    “...As to the title, I think you want formulas to continue to reference the same cells even after those cells have been moved around. But the one last thing I would like to know is how are you randomizing it?...”

    It’s a Menu selection. Just select the range you want to randomize, and then Menu=>Data=>Randomize Range. Each person is given a different randomized spreadsheet to answer just for them.

    Your formula “...=IF(B4>=100,"",E4 + INDEX(E:E,MATCH(B4+100,B:B,0)) + INDEX(E:E,MATCH(B4+200,B:B,0))...”? It works perfectly for the first three, but doesn’t for the next three, and yet changes again for the next three. But these are some functions I’ve never needed to use before, but you may be on the right track. Let me play with it a little bit and see what I can figure out. Thanks for that BTW.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Quote Originally Posted by SpreadSpectrum View Post
    Your formula “...=IF(B4>=100,"",E4 + INDEX(E:E,MATCH(B4+100,B:B,0)) + INDEX(E:E,MATCH(B4+200,B:B,0))...”? It works perfectly for the first three, but doesn’t for the next three, and yet changes again for the next three. But these are some functions I’ve never needed to use before, but you may be on the right track. Let me play with it a little bit and see what I can figure out. Thanks for that BTW.
    Are you putting in the formula before randomizing, or after? If you provide a link to your test file in Google Sheets, this will go a bit faster. I can't test a formula on a picture.

    I do not believe there is a corresponding function in Excel for randomizing data, so I did not realize that was available. I'll look into that in Google Sheets.

  9. #9
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    im curious if you have considered to use Google Forms to create a quiz, which has features to randomize questions and answers?

    although each option wont be grouped, you could use formulas on the results (which will be correctly sorted) to then calculate the formula accuracy.

    furthermore, Google Forms has capacity to mark the tests for you.

    https://support.google.com/a/users/answer/9308764?hl=en
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  10. #10
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    One method to achieve what you want...

    You can "lock the E cell references" by using column B as SUMIF criteria... of course you can also use columns C instead, however, as the formulas are self explanatory i wont be writing all the different changes you might like to try.

    as an example:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    So how many posts must I make in order to attach a link to my Google Sheet? This forum has prohibited me from including a link.

  12. #12
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    the minimum posts for links is 10, but attaching files (following the method in yellow banner above) is immediately available.

    if you post the link with (dot) instead of .
    and (slash) instead of /
    then the mods can fix it back to a valid link for you.

  13. #13
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    I've edited it multiple times now, and it still thinks it's a link. This is post #5. Guess I'll post 5 more times just to get my link out there.

  14. #14
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    I tried what you said janmorris, and it still produced an error message.

  15. #15
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    send it to me in a message, and i will post it for you

    or... if we just chat here a couple more times then we reach the 10 post goal.. yay! haha

  16. #16
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    I guess I assumed it would place a copy automatically into my Sent folder, but apparently not unless I explicitly tell it to. I sent you the link, so I hope you got it janmorris. Thanks so much.

  17. #17
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    hi @SpreadSpectrum

    sorry for the delay, i was on remote island for the week, just got back to civilisation.

    here is the link for your file:

    https://docs.google.com/spreadsheets...gid=1353904934

  18. #18
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    i have entered in the first 3 variations of the formulas into column I.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    note the changing numbers from column C.

    if column D were all unique items then you could use those instead (while also changing the formula cell references from $C$6:$C$306 to $D$6:$D$306)

    now, you just need to copy and paste those formulas down further and make the necessary changes where i highlighted in blue.

    EDIT:

    the formulas in column I will do what is expected, but i see that you will still have the problem of column G not keeping the required cell references that will be added together.

    to fix that, you will need a lookup (or index match) formula written specifically for each and every row (cell) of column G.
    Last edited by janmorris; 05-20-2022 at 12:04 PM.

  19. #19
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    "the formulas in column I will do what is expected, but i see that you will still have the problem of column G not keeping the required cell references that will be added together."

    Actually, it is tracking quite nicely. So wherever C6 moved to, so too did G6 where it is looking up F6 for the result. So I don't have a problem with that it seems.

    Incidentally, this in Column I: =SUMIF($C$6:$C$305,1,$G$6:$G$305)+SUMIF($C$6:$C$305,101,$G$6:$G$305)+SUMIF($C$6:$C$305,201,$G$6:$G$305) is yielding the exact same result as this in Column H: =IF(OR(C6<=0,C6>100),,G6+INDEX(G:G,MATCH(C6+100,C:C,0))+INDEX(G:G,MATCH(C6+200,C:C,0))) even when randomized. So from my viewpoint, either one would work. Am I missing something?

    Now what I need is everything down below, (ie. D315 for example), I need to concatenate everything that equals a value of 3 which is binary 0x011 meaning there are two items which make up each entry that equals a 3. Same for D311, D313, D317, D319, D321, and D323. Any ideas on that one?

  20. #20
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Oh, and thank you for posting the linke!

  21. #21
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    if i randomize the range C6:F14, the values in H and I give incorrect values... where A + AA + AAA should equal 7, it is not.

    but if it is working for you then thats ok.

    ooohhh. i see now.. when you randomize the range C6:G14, then it works.. no problem
    Last edited by janmorris; 05-20-2022 at 04:07 PM.

  22. #22
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Now what I need is everything down below, (ie. D315 for example), I need to concatenate everything that equals a value of 3 which is binary 0x011 meaning there are two items which make up each entry that equals a 3. Same for D311, D313, D317, D319, D321, and D323.
    are the current concatenations how they should appear? or do you need then to look different somehow?
    if different, can you manually create how you want each to look based on the current data, and then i will take a look how i can recreate with formula.

  23. #23
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    OK,

    Columns I & H yield the exact same results. The only difference is that Column I is unlocked and Column H is locked and protected with $ signs. Either will work I think in this case. However, whichever one works best for filling in D311…D323 correctly is what I need to finish this. I know I can figure it out…I just don’t have the time to dredge through it if someone knows a quick down and dirty way to yield the results that I want. I am most grateful for your help.

    About D11…D323. Here is what the results should yield given the current boxes that are checked checked in Column F:

    Row 311…EMPTY. There is no value of 1 calculated.
    Row 313…⬜ ITEM CC
    Row 315…EMPTY, There is no value of 3 calculated.
    Row 317…⬜ ITEM BBB
    Row 319…EMPTY, There is no value of 5 calculated.
    Row 321…EMPTY, There is no value of 6 calculated.
    Row 323…⬜ ITEM A AND ITEM AA AND ITEM AAA.

    In summary, using [XYZ] as an example, we have [XYZ], [XYZXYZ], and [XYZXYZXYZ] as the three possibilities for any given trio of ITEMS.

    If Column H or Column I = 1, then we simply have Row 311 with “⬜ [XYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 2, then we simply have Row 313 with “⬜ [XYZXYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 3, then we simply have Row 315 with “⬜ [XYZ] AND [XYZXYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 4, then we simply have Row 317 with “⬜ [XYZXYZXYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 5, then we simply have Row 319 with “⬜ [XYZXYZXYZ] AND [XYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 6, then we simply have Row 321 with “⬜ [XYZXYZXYZ] AND [XYZXYZ]” and nowhere else for [XYZ].
    If Column H or Column I = 7, then we simply have Row 323 with “⬜ [XYZXYZXYZ] AND [XYZXYZ] AND [XYZ]” and nowhere else for [XYZ].

    Since Columns H & I yield the same results, it should be irrelevant which one we use.

    I hope that all makes sense. If you think of it in binary terms, [XYZ] = 1, [XYZXYZ] = 2, and [XYZXYZXYZ] = 4.

    NONE = 0
    [XYZ] = 1
    [XYZXYZ] = 2
    [XYZ] + [XYZXYZ] = 3
    [XYZXYZXYZ] = 4
    [XYZXYZXYZ] + [XYZ] = 5
    [XYZXYZXYZ] + [XYZXYZ] = 6
    [XYZXYZXYZ] + [XYZXYZ] + [XYZ] = 7

    You get the idea. Hope this helps clear up any confusion. It’s kinda hard to explain.

    Thanks again for your help and feedback.

  24. #24
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Haven't figured this editing thing out for the forum yet. I forgot to add, I hid Rows 16...305. I don't have any boxes checked below Item C, in Column F, so it wouldn't yield any results in the aforementioned Rows below.

  25. #25
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Hi SpreadSpectrum,

    ive been trying to understand what you want to be in cells D311 down...

    for example it says if value of 1 then put that item, if value of 2 then put that item...
    but currently for value of 2 for it shows CCC, but i thought it should be CC

    can you please confirm what the values should be by manually placing A,AA,AAA,B,BB,BBB,C,CC,CCC into cells G311 and down.

  26. #26
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Thanks. I took out the concatenate statements and put in verbatim what it should look like whether Rows 6..305 are randomized or not. If the boxes are checked in Column F regardless of where they appear, the results should not change randomized or not. Hope that helps.

  27. #27
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Great, that helps a lots.

    My analysis of what i see happening...

    Where column H shows a 7 (next to ITEM A), it means that all 3 items for the group (1, 101, 201) have been selected, and therefor you want that group concatenated, and also any other group that has the 7.

    Similarly, where H shows 4 (next to ITEM B), it means that only the last item of the group (202) has been selected, and therefor you want that item concatenated, and also any other item that has the 4.

    So, taking this further, if it was 6, then we want the 2nd and 3rd items of each group where there is a 6.
    And 1st and 3rd items of each group where there is a 5..
    And so on and so forth.

    Is that correct?

    If so, do you want just 1 grey square at the start of the concatenation, or do you want a grey square at the start of every concatenated set?
    so for example do you want:
    ⬜ Item A and Item AA and Item AAA and Item X and Item XX and Item XXX
    or do you want:
    ⬜ Item A and Item AA and Item AAA ⬜ Item X and Item XX and Item XXX
    or something else?

    Sorry for asking these questions, i just want to make sure i understand this clearly.
    Last edited by janmorris; 05-27-2022 at 05:13 AM.

  28. #28
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    "...Is that correct?..."

    YES

    This how I want it to look.

    "...
    ⬜ Item A
    AND
    Item AA
    AND
    Item AAA
    [A LINE SPACE BETWEEN THEM, BUT NOT NECESSARY HERE]
    ⬜ Item X
    AND
    Item XX
    AND
    Item XXX
    ..."

    I hope that makes sense. Please don't apologize for asking questions. The only stupid ones are the ones left unasked.

  29. #29
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    I just want to give you update that i havent left this problem.

    i expanded the range to be A-F, this gives me a better visual of the problem, and how i can attack it.

    i have extended the column I formulas to cover A-F (1-6).

    it looks like the column H formulas arent properly tracking the randomisation.

    anyhows, i just wanted to let you know im still working on it.

  30. #30
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    That's fine. I appreciate all the help I can get on it. Conceptually, I know what I want it to do. I just can't figure out a way to do it without complicating it so.

  31. #31
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    more questions....

    Will there ever be more than say 90 sets of questions?
    How critical is it that column C identifiers for the questions use the existing format?

    The reason for these questions is that im considering how i can use a standard 3 digit code throughout, which could make it easier to extract a portion that identifies each set.

    like this: (question set first, question number last, allows 89 question sets)
    101, 102, 103, 111, 112, 113, 121, 122, 123, 131, 132, 133
    being: 10-1, 10-2, 10-3, 11-1, 11-2, 11-3, 12-1, 12-2, 12-3, 13-1, 13-2, 13-3

    OR (question number first, question set last, allows 99 question sets)
    being: 1-01, 2-01, 3-01, 1-02, 2-02, 3-02, 1-03, 2-03, 3-03, 1-04, 2-04, 3-04


    thinking further, the score could form part of the numbering...

    101, 102, 104, 111, 112, 114, 121, 122, 124, 131, 132, 134
    being: 10-1, 10-2, 10-4, 11-1, 11-2, 11-4, 12-1, 12-2, 12-4, 13-1, 13-2, 13-4

    OR (question number first, question set last, allows 99 question sets)
    being: 1-01, 2-01, 4-01, 1-02, 2-02, 4-02, 1-03, 2-03, 4-03, 1-04, 2-04, 4-04
    Last edited by janmorris; 05-30-2022 at 04:45 AM.

  32. #32
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    “Will there ever be more than say 90 sets of questions?”

    Yes, but it will be a total of 300 questions (100 sets). I don’t want it getting any larger than that because people lose interest and just quit answering.

    “How critical is it that column C identifiers for the questions use the existing format?”

    It’s not. I just used that format because it made sense to me at the time. I don’t really care as long as it works the way that I intended it to work.

    What is important in compiling results is the content in Column D, what box is checked in Column F, and what other boxes are checked in that set. Since each set only has 3 corresponding questions, the total possibilities are between 0-7, or binary 000 - 111 in Column I. I don’t even need that if you have a better way to come up with it. It just made logical sense to me to do it that way.

    “The reason for these questions is that im considering how i can use a standard 3 digit code throughout, which could make it easier to extract a portion that identifies each set.

    like this: (question set first, question number last, allows 89 question sets)
    101, 102, 103, 111, 112, 113, 121, 122, 123, 131, 132, 133
    being: 10-1, 10-2, 10-3, 11-1, 11-2, 11-3, 12-1, 12-2, 12-3, 13-1, 13-2, 13-3

    OR (question number first, question set last, allows 99 question sets)
    being: 1-01, 2-01, 3-01, 1-02, 2-02, 3-02, 1-03, 2-03, 3-03, 1-04, 2-04, 3-04”

    That would be fine if not for the limitation to 89 sets. I could easily have more questions, and I do that sometimes rotating them in and out of the test. Using your model, I would need to expand it to do at least 300 questions (100 sets). Either way, I do want a little flexibility if I ever do choose to go beyond 100 question sets.

    “thinking further, the score could form part of the numbering...

    101, 102, 104, 111, 112, 114, 121, 122, 124, 131, 132, 134
    being: 10-1, 10-2, 10-4, 11-1, 11-2, 11-4, 12-1, 12-2, 12-4, 13-1, 13-2, 13-4

    OR (question number first, question set last, allows 99 question sets)
    being: 1-01, 2-01, 4-01, 1-02, 2-02, 4-02, 1-03, 2-03, 4-03, 1-04, 2-04, 4-04”

    Whatever scoring solution works best, as long as it yields the desired outcome based on the answers to the questions no matter where they are, I don’t really care. Theoretically, no matter how the questions are randomized, the results must be the same as long as the same boxes are checked.

  33. #33
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    That would be fine if not for the limitation to 89 sets. I could easily have more questions, and I do that sometimes rotating them in and out of the test. Using your model, I would need to expand it to do at least 300 questions (100 sets). Either way, I do want a little flexibility if I ever do choose to go beyond 100 question sets.
    ok, for the number of sets, we need enough digits to cover them, so for 100 sets we need 3 digits (this will cover 001-999 sets), then with each set of questions being 3, we need just 1 digit (which covers 1-9 questions).

    so, based on your requirements the code would be a total of 4 digits long.

    if the maximum sets will never exceed 899, then we can put the sets first (because we cant use the first 99 that begin with 0), but if the total sets could reach 999, then the sets part of the code must be after the question code (so that we can use the first 001-099 sets)

    Whatever scoring solution works best, as long as it yields the desired outcome based on the answers to the questions no matter where they are, I don’t really care. Theoretically, no matter how the questions are randomized, the results must be the same as long as the same boxes are checked.
    yiup, thats the goal, hahaha... im not entirely sure how i will attack it yet, at the moment im working out what i need in place in order make certain strategies possible to attempt.

    the difficulty is the randomized from which we have to find certain matches which is why needing a coding system that can be pulled apart, and then find a way to concatenate it so that only the first item of a set has the ⬜ in front.

    i suspect that the solution is going to be quite "dirty".... maybe requiring a bunch of "helpers" that can be reconstructed into the final solution.

  34. #34
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    “ok, for the number of sets, we need enough digits to cover them, so for 100 sets we need 3 digits (this will cover 001-999 sets), then with each set of questions being 3, we need just 1 digit (which covers 1-9 questions).

    so, based on your requirements the code would be a total of 4 digits long.

    if the maximum sets will never exceed 899, then we can put the sets first (because we cant use the first 99 that begin with 0), but if the total sets could reach 999, then the sets part of the code must be after the question code (so that we can use the first 001-099 sets)”

    Yes, this is perfect. I will NEVER EVER have more than 899 questions, nor will I EVER have more than 9 questions in any given set. So this is great!

    “yiup, thats the goal, hahaha... im not entirely sure how i will attack it yet, at the moment im working out what i need in place in order make certain strategies possible to attempt.

    the difficulty is the randomized from which we have to find certain matches which is why needing a coding system that can be pulled apart, and then find a way to concatenate it so that only the first item of a set has the ⬜ in front.”

    If it makes any difference, I don’t care if the concatenate yields:

    ⬜ ITEM A
    AND
    ITEM AA
    AND
    ITEM AAA

    ⬜ ITEM X
    AND
    ITEM XX
    AND
    ITEM XXX

    for example or

    ⬜ ITEM A
    AND
    ⬜ ITEM AA
    AND
    ⬜ ITEM AAA

    ⬜ ITEM X
    AND
    ⬜ ITEM XX
    AND
    ⬜ ITEM XXX

    Or no ⬜ at all as long as we separate Set A,AA,AAA from X,XX,XXX by a line space or something to make it easier to read. That’s just formatting as far as I’m concerned. So if it creates a problem, let’s just remove it from the equation.

    For what it is worth, I think Column I tells us where the first of any given set resides, so then we could build a concatenate statement from there. We will only have 100 of those to build on, then all we have to do is check to see if Column I is 0-7 to determine where it falls below in Rows 311...323. Here is where i began toying with that idea if it gives you any thoughts on the matter (NOTE: I was just trying to see what it would yield for testing purposes as opposed to what it would actually yield once implemented). I also discovered long ago that for any given cell, there is a limitation of 50,000 characters, so for intense lengthy concatenate statements, I have to break them up sometimes into smaller ones and then concatenate the smaller ones together. You can see where if I had 300 of this one below, it would rapidly approach 50,000 characters (39,000 in this particular case):

    =if(I6=0,"ZERO",if(I6=1,"ONE",if(I6=2,"TWO",if(I6=4,"FOUR",if(I6=3,"THREE",if(I6=5,"FIVE",if(I6=6,"SIX",if(I6=7,"SEVEN",I6))))))))

    You have no idea how grateful I am for your interest in tackling this. I have racked my brain on it, and I know I could figure it out if only I had the time to do it. From all that I’ve seen so far, it is a rather convoluted process to create. Thank you so much for your help.

  35. #35
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Or no ⬜ at all as long as we separate Set A,AA,AAA from X,XX,XXX by a line space or something to make it easier to read. That’s just formatting as far as I’m concerned. So if it creates a problem, let’s just remove it from the equation.
    awesome, so provided i can devise a way to do the concat, we have some room to move... less headaches if i happen to chance upon something remotely decent, haha.

    You have no idea how grateful I am for your interest in tackling this. I have racked my brain on it, and I know I could figure it out if only I had the time to do it. From all that I’ve seen so far, it is a rather convoluted process to create. Thank you so much for your help.
    no problem
    i like a bit of a challenge with problem solving

  36. #36
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    column I is giving us the total of each set, in their original order, so the top one is 7 means that all three questions of A,AA,AAA have been selected.

    what i need to devise is:
    1. for any item that is 1, concatenate, according to their code. the reason for using the code is that they are the references, and the questions can be any text you like.. so something like a filter, or index match, or vlookup
    2. concatenate each set of the items with CHAR(10) between them

    and use the same kind of process for 2,3,4,5,6,7

  37. #37
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    Yes.

    And the reason for the order in my lengthy formula above was this:

    The first IF was to check for ZERO. This is really so that I do absolutely nothing with it because nothing in the set was selected.

    The second, third, and forth were to check for ONE, TWO, and FOUR. Decimal 1 = Binary 001, Decimal 2 = Binary 010, and Decimal 4 = Binary 100 respectively. This is if only one of the three items were selected.

    The fifth, sixth, and seventh were to check for THREE, FIVE, and SIX. Decimal 3 = Binary 011, Decimal 5 = Binary 101, and Decimal 6 = Binary 110 respectively. This is if two of the three items were selected.

    The last IF was to check for SEVEN. Decimal 7 = Binary 111. This is if all three are selected.

    I think you have a clear understanding where I am going with this. And perhaps that isn't the best route for organizing the information for the concatenate statement.

    I'm sure there are several different ways to go about this. Just not sure what the best one is. Another thought that may be better though I haven't really put much thought into it is to check (if statement) first if all three are selected, and then concatenate all three in order.

    The second check would be to see which ones have more than one selected. Since we already checked for a 7, we simply check which, whether ITEM A, or ITEM AA was first, and concatenate accordingly. You didn't randomize Column H with your randomization, but it tracks and parallels the calculations in Column I if you do Randomize it together with the rest. Column H may hold the key to collecting the content in Column D such that we can place them in order. Haven't played with it enough to figure out quite yet. Like I stated above, our options here are THREE, FIVE, & SIX.

    The third check would be to see which ones have only one selected. We already checked for 7, then checked for 3, 5, & 6. so now we can check for 1, 2, & 4. Then we only have to concatenate one item no matter where it happens to reside.

    I don't know. Maybe I'm overthinking it. Anyhow, you definitely see what I'm trying to accomplish. Thanks again.
    Third check would be to see if

  38. #38
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    regarding column H randomisation, that could be done, however then you will loose the order or the questions, and i cant see a benefit to randomising H....

    i have an idea in mind.. which i will start developing. on a copy tab.

    it will be based on the assumption that every set has exactly 3 questions.

    EDIT:
    now i see why H hasnt got the correct numbers,

    i will go back and fix that once i setup the framework for the idea i have in mind
    Last edited by janmorris; 06-01-2022 at 11:07 AM.

  39. #39
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,066

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    ok, making some progress to prepare everything....

    i made a copy of the TEST sheet.

    then, in column A, to autonumber 300 rows, i entered this into A6:
    Please Login or Register  to view this content.
    EDIT, A6 now modified to:
    Please Login or Register  to view this content.
    on new sheet named "Q&Set Codes" i created the beginnings of a table in columns A:B

    and a Code generator in column G, from which you copy the values and paste into column C of the Questionnaire sheet, in this case "Copy of TEST BED"

    then in column G, this arrayformula:
    Please Login or Register  to view this content.
    so you do not randomize column G, just randomize columns C:F

    then i added conditional formatting to identify the question 1,2,3

    EDIT:

    new formula added to I6, based on the original SUMIF(), that allows you to copy into every 3rd row and it will increase the set number by 1 each time.
    Please Login or Register  to view this content.
    Last edited by janmorris; 06-01-2022 at 01:32 PM.

  40. #40
    Registered User
    Join Date
    05-11-2022
    Location
    USA
    MS-Off Ver
    Unspecified
    Posts
    18

    Re: Formula must reference same cells after those cells are shuffled [Google Sheets]

    OK, Thanks for all the help. Here was the ultimate solution. It works every time. Maybe I'm just overthinking it and there is a better way. Please don't go back and try to figure it out with the sample SHEET I provided. I've abandoned it and have made some other modifications that would render it difficult to decipher.

    I adopted the ITEM Numbering scheme as follows in Column B
    1
    1001
    2001
    2
    1002
    2002
    3
    1003
    2003
    ...

    All of the ITEMS descriptions are listed in Column C
    ...

    Column D contains all of the selections to determine the value if a box is checked in Column E.
    =if(E8,1,)
    =if(E9,2,)
    =if(E10,4,)
    =if(E11,1,)
    =if(E12,2,)
    =if(E13,4,)
    =if(E14,1,)
    =if(E15,2,)
    =if(E16,4,)
    ...

    Moving all the way down to ROW 1001, for every ITEM, I used COLUMN B again with the following formula to calculate the value of a 3 question set (Range 0 - 7):
    =IF(OR(B8<=0,B8>1000),,D8+INDEX(D:D,MATCH(B8+1000,B:B,0))+INDEX(D:D,MATCH(B8+2000,B:B,0)))
    =IF(OR(B9<=0,B9>1000),,D9+INDEX(D:D,MATCH(B9+1000,B:B,0))+INDEX(D:D,MATCH(B9+2000,B:B,0)))
    =IF(OR(B10<=0,B10>1000),,D10+INDEX(D:D,MATCH(B10+1000,B:B,0))+INDEX(D:D,MATCH(B10+2000,B:B,0)))
    ...
    For each ITEM, I used the detmined value in COLUMB B to FORMAT the information in the proper format as follows (YES, I know it's a long formula, but it does work even when I randomize the data set between B8:E307):
    =if(B1001=0,,
    if(B1001=1,concatenate("⬜ ",index(C:C,Match(B8,B:B,0))),
    if(B1001=2,concatenate("⬜ ",index(C:C,Match(B8+1000,B:B,0))),
    if(B1001=4,concatenate("⬜ ",index(C:C,Match(B8+2000,B:B,0))),
    if(B1001=3,concatenate("⬜ ",index(C:C,Match(B8,B:B,0)),"
    AND
    ","⬜ ",index(C:C,Match(B8+1000,B:B,0))),
    if(B1001=5,concatenate("⬜ ",index(C:C,Match(B8,B:B,0)),"
    AND
    ","⬜ ",index(C:C,Match(B8+2000,B:B,0))),
    if(B1001=6,concatenate("⬜ ",index(C:C,Match(B8+1000,B:B,0)),"
    AND
    ","⬜ ",index(C:C,Match(B8+2000,B:B,0))),
    if(B1001=7,concatenate("⬜ ",index(C:C,Match(B8,B:B,0)),"
    AND
    ","⬜ ",index(C:C,Match(B8+1000,B:B,0)),"
    AND
    ","⬜ ",index(C:C,Match(B8+2000,B:B,0))),))))))))
    ...

    Finally, I connect the dots from above with the following to show the results (This was for a calculation of 1. I repeated this pattern for 2-7):
    =concatenate(if(B1001=1,concatenate(C1001,"
    "),),if(B1002=1,concatenate(C1002,"
    "),),if(B1003=1,concatenate(C1003,"
    "),),if(B1004=1,concatenate(C1004,"
    "),),if(B1005=1,concatenate(C1005,"
    "),),if(B1006=1,concatenate(C1006,"
    "),),if(B1007=1,concatenate(C1007,"
    "),),if(B1008=1,concatenate(C1008,"
    "),),if(B1009=1,concatenate(C1009,"
    ...
    "),),if(B1292=1,concatenate(C1292,"
    "),),if(B1293=1,concatenate(C1293,"
    "),),if(B1294=1,concatenate(C1294,"
    "),),if(B1295=1,concatenate(C1295,"
    "),),if(B1296=1,concatenate(C1296,"
    "),),if(B1297=1,concatenate(C1297,"
    "),),if(B1298=1,concatenate(C1298,"
    "),),if(B1299=1,concatenate(C1299,"
    "),),if(B1300=1,concatenate(C1300,"
    "),))

    Anyway, it may not be the most elegant solution, but it does work. I'm OPEN to anybody that has a cleaner solution, as this was a royal pain in my behind. Again thanks for all of the help.

+ 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. Google Sheets. Can I connect offline data from excel to google sheets?
    By drlemur39 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 11-15-2021, 10:40 AM
  2. (Google App Scripts) Copy and paste value for all Google Sheets contained in one folder
    By bobbiekan in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 09-29-2021, 11:48 PM
  3. Replies: 0
    Last Post: 01-30-2021, 05:44 AM
  4. [SOLVED] Google sheets vba to download file and save it to google drive folder and use it's data
    By western in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2021, 08:48 AM
  5. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  6. Google Sheets: Formula to figure out distance from target
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-14-2018, 09:41 AM
  7. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM

Tags for this Thread

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