2 "Is it possible to do this in Excel97" questions?

[Adam Kroger]

Can you change the value of a cell from another cell?

i.e.
IF A1=3
then B2=4
else C2=3


also

can you set up a temporary "variable" in a cell to make functions more
streamlined. For example, I have the following function in my worksheet:

=IF(AND(ISTEXT(A4),ISTEXT(D4),ISTEXT($E$1)),IF(VLOOKUP($E$1,CHOOSE($B$2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="W",1,IF(OR(VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="R",
VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="M"),2,IF(VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="J",3))),"")

basically what it does is check to see what value is in a certain cell on
another sheet (either M,J,R,or W) and then return eihter 1,2 or 3. If I
could assign assign a variabe, then the whole process could be completed
without multiple lookups. I ask because I have another similar situation,
but to get the IF/THEN to work right, the fonction entry is too long for
excel to handle. I currently have the pieces of the other process split
into several other cells but would like to get them into just 1.

----------------read further if you need more info------------------

Sheet1:
has initial numerical value in a table
Sheet2:
Sheet3:
Cell 1:
Check to make sure a number from sheet1 is there, and perform a calc
on it (2 VLOOKUPS)
Cell 2:
Same as cell 1 but a different calculation
Cell 3a-3e (aranged to be available for a VLOOKUP):
Same process as Cell 1, but is pulling data from a cell on Sheet2
and either cell 1 or Cell 2 for the calc
Cell 4 (repeated 36 times on 11 more sheets):
Nested IF (5 levels) to pull the correct data based on what a 5th
cell has as text.

If I could assign "temporarrily" variables inside the cell, then this could
be accomplished alot eassier. I have a feeling the answer is going to be
VBA

If anyone is interested in helping me streamline this, I would appreciate
it. The actual speardsheet can be found on Kazaa as "Battletech - GM -
Combat Tracker v2.0" Cell 4 is Pilot 1!B14

There are alot more neat/nifty things I am wanting to do wiht it, but I
think I have reached the end of what I can accomplish without VBA functions.

[Bill Martin]

Adam Kroger <adam_kroger wrote:
> Can you change the value of a cell from another cell?
>
> i.e.
> IF A1=3
> then B2=4
> else C2=3
>
>
> also
>
> can you set up a temporary "variable" in a cell to make functions more
> streamlined. For example, I have the following function in my worksheet:
>
> =IF(AND(ISTEXT(A4),ISTEXT(D4),ISTEXT($E$1)),IF(VLOOKUP($E$1,CHOOSE($B$2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
> MATCH("Typ",rounds_heading,0),0)="W",1,IF(OR(VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
> MATCH("Typ",rounds_heading,0),0)="R",
> VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
> MATCH("Typ",rounds_heading,0),0)="M"),2,IF(VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
> MATCH("Typ",rounds_heading,0),0)="J",3))),"")
>
> basically what it does is check to see what value is in a certain cell on
> another sheet (either M,J,R,or W) and then return eihter 1,2 or 3. If I
> could assign assign a variabe, then the whole process could be completed
> without multiple lookups. I ask because I have another similar situation,
> but to get the IF/THEN to work right, the fonction entry is too long for
> excel to handle. I currently have the pieces of the other process split
> into several other cells but would like to get them into just 1.
-----------------

You cannot change cell B2 from a formula in some other cell. To do that you'd
need to learn a little VBA macro programming -- probably an event driven routine
that gets called when cells B2 or C2 change, though it depends on what you're
trying to accomplish.

But on the plus side, yes you can assign an unused cell somewhere to be a
temporary variable. Say you have a worksheet named "TEMP", then you could make
various cells on it contain your individual long IF(..) statements. And then
have a much shorter final formula that references the temporary cells.

Bill