Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula - Is it possible to...
For each cell in column B I want to test if the adjacent cell in column A is
10 and if it is set the B cell the same as value in the A cell (this bit is easy) and also set a cell in column C with the value "100". However the reference of the column C cell is dependent upon the value (between 1 - 4) in the adjacent cell in column D. For example (I know this isn't possible but it may help to explain the idea), applying the formula in B1: if(A110,"20", C(D1)="100"). If A1 was 8 and D1 = 4 then C4 is set to 100 and if D1 was 2 then C2 is set to 100. What I really want to know is is it possible to use integers from another cell to reference a cell? Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula - Is it possible to...
You can use the INDIRECT function to reference a cell, so INDIRECT("C"&D1)
would reference C4, and allow you to read a value from that cell, but a formula in one cell won't set a value in another cell, so a formula in B1 can't write to C4. If you want a formula to set a value in a cell, you need to put the formula in that cell. Hence in C4 you could put =IF(AND(A110,D1=4),100,"") Note that if you want the number 10 you don't surround it with quote arks. "100" would give a text string, not a number. -- David Biddulph "Beginner-Bob" wrote in message ... For each cell in column B I want to test if the adjacent cell in column A is 10 and if it is set the B cell the same as value in the A cell (this bit is easy) and also set a cell in column C with the value "100". However the reference of the column C cell is dependent upon the value (between 1 - 4) in the adjacent cell in column D. For example (I know this isn't possible but it may help to explain the idea), applying the formula in B1: if(A110,"20", C(D1)="100"). If A1 was 8 and D1 = 4 then C4 is set to 100 and if D1 was 2 then C2 is set to 100. What I really want to know is is it possible to use integers from another cell to reference a cell? Any help would be much appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula - Is it possible to...
You can refer to a cell's value.
So if E1 has value 5 and E2 have value 20, the the formula =SUM(INDIRECT("A"&D1&":A"&D2)) will be evaluated as =SUM(A5:A20) But a formula in one cell cannot change the value in another cell So you formula in B1 could never alter what is in C1 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Beginner-Bob" wrote in message ... For each cell in column B I want to test if the adjacent cell in column A is 10 and if it is set the B cell the same as value in the A cell (this bit is easy) and also set a cell in column C with the value "100". However the reference of the column C cell is dependent upon the value (between 1 - 4) in the adjacent cell in column D. For example (I know this isn't possible but it may help to explain the idea), applying the formula in B1: if(A110,"20", C(D1)="100"). If A1 was 8 and D1 = 4 then C4 is set to 100 and if D1 was 2 then C2 is set to 100. What I really want to know is is it possible to use integers from another cell to reference a cell? Any help would be much appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula - Is it possible to...
What I really want to know is is it possible to
use integers from another cell to reference a cell? Try explaining, as best you can, what it is you ultimately want to do. For example: I want to sum a certain range of cells in column A starting from cell A1. The number of cells to sum will be entered in cell B1. If B1 = 5 then I want to sum the range A1:A5. If B1 = 20 then I want to sum the range A1:A20. It's impossible to not understand an explanation when it's written like that! -- Biff Microsoft Excel MVP "Beginner-Bob" wrote in message ... For each cell in column B I want to test if the adjacent cell in column A is 10 and if it is set the B cell the same as value in the A cell (this bit is easy) and also set a cell in column C with the value "100". However the reference of the column C cell is dependent upon the value (between 1 - 4) in the adjacent cell in column D. For example (I know this isn't possible but it may help to explain the idea), applying the formula in B1: if(A110,"20", C(D1)="100"). If A1 was 8 and D1 = 4 then C4 is set to 100 and if D1 was 2 then C2 is set to 100. What I really want to know is is it possible to use integers from another cell to reference a cell? Any help would be much appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula - Is it possible to....
Thanmks qll for your responses, I now know that I can't do what I wanted to do and will have to look at the problem in another way. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|