Posers and Puzzles

Posers and Puzzles

  1. Joined
    26 Apr '03
    Moves
    26533
    03 Mar '04 17:352 edits
    This puzzle may be fun for those of you that use m$ excel:

    The user inputs data only in A1

    Using formulas only (NO macros) arrange that when the user changes A1, cell B1 always displays what was in A1 before the change.

    i.e:
    Start, A1 is blank
    User inputs "Fish" -> B1 is blank
    User inputs "Chips" -> B1 = "Fish"
    User inputs "Chips" -> B1 = "Fish"
    User inputs "1" -> B1 = "Chips"
    User inputs "2" -> B1 = "1"

    Your answer must detail the formulas you need to achieve this, in sufficient detail that I can input your solution into Excel to see if it works.
  2. Joined
    26 Apr '03
    Moves
    26533
    04 Mar '04 22:49
    Originally posted by iamatiger
    This puzzle may be fun for those of you that use m$ excel:

    The user inputs data only in A1

    Using formulas only (NO macros) arrange that when the user changes A1, cell B1 always displays what was in A1 before the change.

    i.e:
    Start, A1 is blank
    User inputs "Fish" -> B1 is blank
    User inputs "Chips" -> B1 = "Fish"
    User inputs "Chips" -> B ...[text shortened]... achieve this, in sufficient detail that I can input your solution into Excel to see if it works.
    Anyone tried this yet? Let me know if you need a clue...
  3. Amsterdam
    Joined
    26 Jan '03
    Moves
    27540
    08 Mar '04 06:57
    I think I do need a clue, I've tried everything, but I still can't get it!

    Olav
  4. Zeist, Holland
    Joined
    11 Sep '03
    Moves
    19337
    08 Mar '04 11:49
    I doubt if it is possible at all, since Excel updates all cells at once. So it would be very hard to make a buffer, without using macros.
  5. Joined
    26 Apr '03
    Moves
    26533
    08 Mar '04 14:05
    Here's a clue:
    In Tools/Options/Calculation, enable iteration.

    Another clue available in two days if still stumped.
  6. Joined
    26 Apr '03
    Moves
    26533
    10 Mar '04 16:19
    Originally posted by iamatiger
    Here's a clue:
    In Tools/Options/Calculation, enable iteration.

    Another clue available in two days if still stumped.
    Want the next clue then folks?
  7. Amsterdam
    Joined
    26 Jan '03
    Moves
    27540
    10 Mar '04 17:09
    Yup, that would be nice, I thought I knew everything about Excel... 😕

    Olav
  8. Joined
    26 Apr '03
    Moves
    26533
    10 Mar '04 19:001 edit
    Originally posted by LivingLegend
    Yup, that would be nice, I thought I knew everything about Excel... 😕

    Olav
    Ok - You require at least two formulas. Obviously a formula in B1, which is the cell tracking the last value in A1, but another formula in another cell too (eg C1). Without iteration enabled excel will tell you that these formulas have "circular references" and will not evaluate them, as they each reference their own and each others contents.

    Next clue available in two more days on request. 🙂
  9. Joined
    26 Apr '03
    Moves
    26533
    14 Mar '04 17:49
    Originally posted by iamatiger
    Ok - You require at least two formulas. Obviously a formula in B1, which is the cell tracking the last value in A1, but another formula in another cell too (eg C1). Without iteration enabled excel will tell you that these formulas have "circular references" and will not evaluate them, as they each reference their own and each others contents.

    Next clue available in two more days on request. 🙂
    Ok- Want the last, and easiest clue?
  10. Joined
    31 Jan '04
    Moves
    331
    15 Mar '04 21:06
    The suspence is killing me. 🙂
    What's the final clue?
  11. Joined
    26 Apr '03
    Moves
    26533
    16 Mar '04 00:22
    Originally posted by econundrum
    The suspence is killing me. 🙂
    What's the final clue?
    Ok - With the user entering words in A1, B1 displaying the last word entered and an extra formula in C1 (+ with iteration switched on) the following characters can be used to construct the formulas required for the shortest (I think) possible solution:

    ====(()),,,,11111111AABBCCCCFFII
  12. Joined
    26 Apr '03
    Moves
    26533
    24 Mar '04 18:341 edit
    Originally posted by iamatiger
    Ok - With the user entering words in A1, B1 displaying the last word entered and an extra formula in C1 (+ with iteration switched on) the following characters can be used to construct the formulas required for the shortest (I think) possible solution:

    ====(()),,,,11111111AABBCCCCFFII
    Has everyone given up on this? Come on folks, I think a bit of experimentation should sort it for you after that clue...
Back to Top