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(A1>10,"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.
0
Utf
12/16/2009 6:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
872 Views

Similar Articles

[PageSpeed] 35

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(A1>10,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" <BeginnerBob@discussions.microsoft.com> wrote in message
news:B5367737-6D13-4A76-9145-43DCF6F4C610@microsoft.com...
> 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(A1>10,"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.



0
David
12/16/2009 6:35:23 PM
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" <BeginnerBob@discussions.microsoft.com> wrote in message 
news:B5367737-6D13-4A76-9145-43DCF6F4C610@microsoft.com...
> 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(A1>10,"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. 

0
Bernard
12/16/2009 6:36:35 PM
>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" <BeginnerBob@discussions.microsoft.com> wrote in message 
news:B5367737-6D13-4A76-9145-43DCF6F4C610@microsoft.com...
> 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(A1>10,"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. 


0
T
12/16/2009 6:43:32 PM
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. 
0
Utf
12/17/2009 2:59:01 PM
Reply:

Similar Artilces: