Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 "Is it possible to do this in Excel97" questions?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 "Is it possible to do this in Excel97" questions?
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(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. ----------------- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A few questions if anyone has a moment? | Excel Worksheet Functions | |||
A couple of questions... | Excel Discussion (Misc queries) | |||
Questions??? | Excel Discussion (Misc queries) | |||
Pivot Table for survey data w/ questions as Rows & poss answrs as | Excel Discussion (Misc queries) | |||
Questions about MS Word | Excel Discussion (Misc queries) |