assigning numbers to text and count them

[gimme_donuts]

This is a follow up for a question i asked 1 month earlier and somehow I cant
reply anymore...so here goes again :

hey is there a way to simplify the formula
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1) ????

what I mean is that now, I have added more than 200 different objects with
each of them having 7

cells/options that have a validation and which are then summed with the
above formula.

BUT now I want to make the sum of each of the cells/options. So the formula
would look like "INDIRECT(B1)

+INDIRECT(B2)" until "+INDIRECT(B200)" which would be very long...
so my question is : is there a way to make it shorter and easier to handle???

 

[Per Jessen]

Hi

This UDF should do it:


Function SumIndirect(FirstCell As Range, LastCell As Range) As Double
Application.Volatile
If FirstCell.Row <> LastCell.Row Then Exit Function
TargetRow = FirstCell.Row
FirstCol = FirstCell.Column
LastCol = LastCell.Column
Set targetRange = Range(Cells(TargetRow, FirstCol), Cells(TargetRow,
LastCol))
For Each cell In targetRange.Cells
s = s + Range(cell.Value).Value
Next
SumIndirect = s
End Function

Regards,
Per

 

[muddan madhu]

try this

=SUM(INDIRECT(OFFSET(INDIRECT("B"&1),,,1)&":"&OFFSET(INDIRECT
("B"&200),,,1)))

 

[gimme_donuts]

hi Per,

i'm not sure I understand how to use this. I pasted it into the VB window,
but what should I use in excel?
I tried =SumIndirect(F4,F400) but it isnt working ;o)
Thanks for your help!

 

[gimme_donuts]

its not working, only returns the value "1"

try this

=SUM(INDIRECT(OFFSET(INDIRECT("B"&1),,,1)&":"&OFFSET(INDIRECT
("B"&200),,,1)))