Retrieve cell value basis the inputs into a diff cell // sk.

[sansk_23]

Hi !!

If the cell A5 has the value E
and the call B5 has the value 5
The formula CONCATENATE(A5,B5) will give me the result as E5.
How can i automatically retrieve what is the value in the Cell E5 ?

rgds, sansk_23.

 

[T. Valko]

Try this:

=INDIRECT(A5&B5)

 

[Max]

Just use INDIRECT, viz.:
=INDIRECT(A5&B5)

 

[sansk_23]

Thx.
Further if i have the formula for :
North as - SUMIF($C$1:$Z$1,"N",$C$19:$AA$19)
South as - SUMIF($C$1:$Z$1,"S",$C$19:$AA$19)
West as - SUMIF($C$1:$Z$1,"W",$C$19:$AA$19)
East as - SUMIF($C$1:$Z$1,"E",$C$19:$AA$19)

and for the portion "$C$19:$AA$19" from the above formulas,
i want the row no "19" as an input from another cell , ssay A1.
If i change the value of the cell A1 to 20, then the above formulae should
turn to :
North as - SUMIF($C$1:$Z$1,"N",$C$20:$AA$20)

Pls advise.

rgds, sansk_23

 

[Max]

You could apply INDIRECT like this:
=SUMIF($C$1:$Z$1,"N",indirect("C"&A1&":Z"&A1)
where A1 will house the row number, eg: 20

Note that your SUMIF ranges should be consistent
(cols C to Z)

 

[Max]

The formula should read:
=SUMIF($C$1:$Z$1,"N",INDIRECT("C"&A1&":Z"&A1))

(missed out the rightmost closing parens)

---