- 03 Mar '04 17:35 / 2 editsThis 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. - 04 Mar '04 22:49

Anyone tried this yet? Let me know if you need a clue...*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. - 10 Mar '04 19:00 / 1 edit

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.*Originally posted by LivingLegend***Yup, that would be nice, I thought I knew everything about Excel...**

Olav

Next clue available in two more days on request. - 14 Mar '04 17:49

Ok- Want the last, and easiest clue?*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. - 16 Mar '04 00:22

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:*Originally posted by econundrum***The suspence is killing me.**

What's the final clue?

====(()),,,,11111111AABBCCCCFFII - 24 Mar '04 18:34 / 1 edit

Has everyone given up on this? Come on folks, I think a bit of experimentation should sort it for you after that clue...*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