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???

"Joerg Mochikun" wrote:

Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu InsertNameCreatecheck "Left column" OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun



"gimme_donuts" wrote in message
...
it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I
want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is
easely
readable and editable for everyone and it still can make some addition of
it
in the end.

Example:

Kwik e-mart Fruits - Vegetables - Meat - Fish - Fresh
Total
score
Yes Yes Sometimes No Maybe
2.75


To add this data, I used the validation function so that there is a
dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)

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

On 24 Dec., 09:04, gimme_donuts
wrote:
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???



"Joerg Mochikun" wrote:
Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu InsertNameCreatecheck "Left column" OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun

"gimme_donuts" wrote in message
...
it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I
want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is
easely
readable and editable for everyone and it still can make some addition of
it
in the end.

Example:

Kwik e-mart * *Fruits * - *Vegetables *- Meat *- Fish - *Fresh
Total
score
* * * * * * * * * * * * * Yes * * * * *Yes * * * *Sometimes * *No *Maybe
*2.75

To add this data, I used the validation function so that there is a
dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

try this

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


On Dec 24, 1:04*pm, gimme_donuts
wrote:
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???

"Joerg Mochikun" wrote:
Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu InsertNameCreatecheck "Left column" OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun

"gimme_donuts" wrote in message
...
it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I
want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is
easely
readable and editable for everyone and it still can make some addition of
it
in the end.

Example:

Kwik e-mart * *Fruits * - *Vegetables *- Meat *- Fish - *Fresh
Total
score
* * * * * * * * * * * * * Yes * * * * *Yes * * * *Sometimes * *No *Maybe
*2.75

To add this data, I used the validation function so that there is a
dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)

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!

"Per Jessen" wrote:

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

On 24 Dec., 09:04, gimme_donuts
wrote:
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???



"Joerg Mochikun" wrote:
Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu InsertNameCreatecheck "Left column" OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun

"gimme_donuts" wrote in message
...
it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I
want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is
easely
readable and editable for everyone and it still can make some addition of
it
in the end.

Example:

Kwik e-mart Fruits - Vegetables - Meat - Fish - Fresh
Total
score
Yes Yes Sometimes No Maybe
2.75

To add this data, I used the validation function so that there is a
dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

its not working, only returns the value "1"

"muddan madhu" wrote:

try this

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


On Dec 24, 1:04 pm, gimme_donuts
wrote:
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???

"Joerg Mochikun" wrote:
Here is a simplified example, using the power of range names:

Put following texts into A10:A14: Select,Yes,No,Sometimes,Maybe
Now put next to it into B10:B14 the corresponding values: 0,1,0,0.25,0.5

Now select A10:B14 and go to menu InsertNameCreatecheck "Left column" OK
You've just created range names for B10 to B14.

Create a cell validation for cell B1 by using the list in A10:A14 as
criteria.
Choose "Select" from the drop-down list in B1 and copy the cell through D1.

You now should have values "Select" in B1:D1.

Now here comes the meat:
Put following formula into A1:
=INDIRECT(B1)+INDIRECT(C1)+INDIRECT(D1)

This will result in the sum of B1:D1, based on the text is these cells.
Since the formula will result in an error message if any of the cells is
blank, I added the "Select" value as a default (if you want to be nice you
can use "Please_select"). Of course you could also trap the error with an
additional IF condition, but I wanted to keep the formula simple.

Cheers,

Joerg Mochikun

"gimme_donuts" wrote in message
...
it has to be very easy but I have not found the way to do it.

What I'm tryig to do is some list of companies and for their services I
want
to use Yes for 1, No for 0, Sometimes for 0.5 and Maybe for 0.25.

just asigning these numbers to the text is what I want, so that it is
easely
readable and editable for everyone and it still can make some addition of
it
in the end.

Example:

Kwik e-mart Fruits - Vegetables - Meat - Fish - Fresh
Total
score
Yes Yes Sometimes No Maybe
2.75

To add this data, I used the validation function so that there is a
dropdown
menu for each criteria (Yes, No, Sometimes, Maybe)