Constructing a formula

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Hi,

I'm trying to construct a method to *enter* a formula given criteria entered
using vlookups. I've only just started experimenting but already I've run
into a hopefully small problem. I used the formula (cut the vlookup parts
out)

="="&C2&"("&B2&C23&":"&B2&D23&")" which gives a result of

=SUM($L2:$L6)

however this is *apparently* text. If I copy the result from the formula
bar and paste it to another cell it works as it should (summing the 5
cells). Is there a way around this?

My idea is that the end user can select what they want to sum by column name
and the formula will be entered for them. I would also need something
similar for conditional sums as well, and at the moment I'm at a loss
exactly how to do this. Displaying the {} is no problem but that isn't part
of entering the formula as it is done with a ctrl-shift-enter keystroke.

Any help, guidance or pointers to a www site that may help would be greatly
appreciated.

TIA

Ken
 
One way:

You can't build a formula that way, but you can use the INDIRECT()
function to build a range argument:

=SUM(INDIRECT(B2 & C23 & ":" & B2 & D23))

I'm not sure where you're going with conditional sums and array
formulae.
 
EVAL in

=EVAL("="&C2&"("&B2&C23&":"&B2&D23&")")

would invoke the evaluation of the result string as a formula if
well-formed.

You can avoid formula strings that require confirming with
control+shift+enter by constructing equivalent formulas with SumProduct.

EVAL is part of the morefunc.xll add-in
(http://longre/free/fr/english/index.html). If needed, one can be written in
VBA.
 
Thanks J.E. & Aladin, I'll play some more tomorrow. It promises to be an
interesting exercise

Ken
 
Just to be different, this is how I would do it...

[A1].Formula = Replace("=Sum(#)", "#", [B2 & C23 & ":" & B2 & D23])
 
Back
Top