In Lotus, there was an advanced formula "Let cell nn = whatever). IOW you could send an answer to another cell. I've never found out how to do that in Excel. Can write "If" macros but very laborious by comparison. Anyone know what I'm on about?

Many thanks

Mo

Anyone know what I'm on about?

Nope, but if you give me another clue I might be able to help...

Tools, Options, Transition, Transition formula entry?

Not sure if I can. But in Excel, I've only ever found a way of picking up a cell (rather than sending to).

So, in Lotus, you could get a result in a cell, based on another cell (call it base cell). Then have a separate cell with a formula that said that if "base cell" doesn't give the right follow through answer when applied to following data, then "(If - base cell doesn't have right number, "Let" base cell be base cell +1, whatever)

Normally used when manual calculation switched on and iterations switched to "1"

As ever, I know what I mean LOL. I guess it might or might not exist in Excel and maybe it's one of those things that if you've never had, you wouldn't miss. Dunno if that helps at all, but if it does, then thanks again

Mo

Tools, Options, Transition, Transition formula entry?

The conversion option doesn't cover what Lotus called advanced formulas unfortunately. They were several pages on in the handbook and I guess Microsoft probably thought that few knew of them so no point. Fair enough. There's an equivalent for most (or workaround for same) -I've just never found one for the sending rather than receiving. Hope that makes sense.

Mo

Have you tried transition yet? It allows you to use "old" Lotus formulae. If not what are you actually trying to do? It's simple a simple formula to do what you've just put in words:

A3 = 25

A5 = 24

Formula in B5 is =IF(A5=A3,A5,A5+1)

ahh, I think I think I'm with you now, but I'm not sure of the easy way to do it. I'd be writing 'IF' macros for that sort of thing, but like you say its a right PITA.

I'm gonna be puzzling over this all night now:rolleyes: so lets hope someone comes along with an easy solution

You can use IF to achieve what you're after...syntax is thus:

IF(logical_test,value_if_true,value_if_false)

So in the target cell, you could do your evaluation of the "base cell", and then have your formulae for calculating the value of the cell as the other two parameters.

I suspect there could be a way of getting the base cell to have the formulae control the values of external cells, but I couldn't confirm that without some tinkering...

Does that do what you're after?

Rob.

Have you tried transition yet? It allows you to use "old" Lotus formulae. If not what are you actually trying to do? It's simple a simple formula to do what you've just put in words:

A3 = 25

A5 = 24

Formula in B5 is =IF(A5=A3,A5,A5+1)

Yes, but that gives the answer in B5

Rob, ta. Back in a sec.

Mo

out of interest mo, when was the last time you used Lotus 123?

I remember we had it on an ancient PC but not heard anyone talk about it in years.

It must be a sign of age remembering features of it!!

Rob, better make that back in two secs....

Mo

out of interest mo, when was the last time you used Lotus 123?

I remember we had it on an ancient PC but not heard anyone talk about it in years.

It must be a sign of age remembering features of it!!

A while ago. It just drives me barmey ongoing that I have to right a macro every time for something I could have written with a one-line formula. It was only when someone asked a question the other day that I thought, "Hang on, Briskoda is like a Wikipedia all of its own."

Mo

You can use IF to achieve what you're after...syntax is thus:

IF(logical_test,value_if_true,value_if_false)

So in the target cell, you could do your evaluation of the "base cell", and then have your formulae for calculating the value of the cell as the other two parameters.

I suspect there could be a way of getting the base cell to have the formulae control the values of external cells, but I couldn't confirm that without some tinkering...

Does that do what you're after?

Rob.

I'm not sure it does... Going back to basics, the idea is to be able to change a cell by being directed from another cell.

I do understand Ifs (my max was about 18 nestling brackets - stayed up 24/7 to get that one right LOL).

Mo

Sorry mo,

I had a look at this again last night and couldn't see any easy solution in excel so your'll have to get writing those 'IFs'!

If you do find a solution, let me know to put me out of my misery...

There is one in Excel that will iterate to find a number that gives the right answer. The difference is that it could be sent by Lotus to another cell as an absolute, so it didn't constitute a circular reference.

Ta for your efforts. I guess it's still my overriding mission in life to find the answer. There has to be an answer, and, if it's 42, it still needs to arrive as an absolute value in another cell. I'll probably be in a padded cell by the time I find it. I wonder if they'll let me out then?

And I will let you know, promise, though I'm leaving now and could be gone for some time...

Ta

Mo

Ta for your efforts. I guess it's still my overriding mission in life to find the answer.

I don't think you'll be able to do it using the functions Excel provides...from a "good design" point of view it's a Bad Thing what you're proposing, as on a large sheet if you're looking at a number in a cell that's been set by another cell, you won't have any easy way of tracing where it came from.

At least with Excel you can figure out how a value is derived based on the formula associated with that cell...but I digress...

Have you considered writing your own version of Let in VBA?

Rob.

out of interest mo, when was the last time you used Lotus 123?

Last time I used it was in 2004 (Release 9.7 for Windows) and it's still installed on my desktop PC, but that might have something to do with my employer Fortunately they saw sense and realised that Excel was the way the world was going and I get to use that now

No idea on how to help you though, Mo

Chris

Not sure who I'm replying to about this, but yes, agreed that a figure that can't be traced is a "no-no". OTOH, as Excel does offer a tracking system, if it did exist in Excel, then I'm sure it would be tracked.

Gotta go.

Mo