Formula help

[Guest]

Hi,

In cell AI8 I have a CONCATENATE formula to bring back the previous 3 cells,
thus the result shown in cell AI8 would be:-
SUMPRODUCT(--($Q$5:$Q$10001=38626),--($D$5:$D$10001<TIME(12,0,0)))

However, I need to copy/paste the value in AI8 to another cell, but when I
copy all I get is the original formula copied, ie the
=CONCATENATE(AF8,AG8,AH8) !!!

Wot am I doing wrong, can you help
Thanks in advance

 

[Guest]

When you do a concatenate you get a "text" value not a cell reference. So
=concatenate("A","1") gives you a value of "A1" Rather than a the value of
cell A1. To get around this you need to use the indirect function to change
the text to a reference. Try something like =indirect(concatenate("A","1"))
and you will get the value of cell A1.

 

[Guest]

hI,
Thanks for help, two problems
1) if I'm not mistaken using the =indirect function meens that the "A1"
value will never be changed, ie if I cop/paste/drag the formula down a
column, the result will always show the value of A1, when infact I need
A1:A1000.
2) how do I get the =indirct function to show 3 cells?
ie with =indirect(concatenate("A","1")) this will return the value of cell
A1 and I need the values of A1:C1 shown.
Thanks again