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(VLO OKUP($E$1,CHOOSE($B$2,rn1mw,rn2mw,rn3mw,rn4mw,rn5m w,rn6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="W",1,IF(OR(VLOOK UP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5mw,rn 6mw,rn7mw,rn8mw,rn9mw,rn10mw,rn11mw,rn12mw),
MATCH("Typ",rounds_heading,0),0)="R",
VLOOKUP($E$1,CHOOSE(B2,rn1mw,rn2mw,rn3mw,rn4mw,rn5 mw,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,rn6m w,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.