Substitute text with assigned values
 

I have a table as below:

A B C D E
1 Value 2 9 5 Total
2 Item 1 x
3 Item 2 x x
4 Item 3 x
5 Item 4

Whereever x is plotted, I need the cell to be assigned values from the
corresponding row above.
Eg. Item 2 is marked x in col B, I want the cell B3 to have a value 2 even
though x can be seen. Similarly item 2 is marked x in Col D, should now have
a value 5.
I need to sum up these values in the total column E.

Do reply.

Substitute text with assigned values
 

Perhaps something like this:

E2: =SUMIF(B2:D2,"x",B$1:D$1)
copy that formula down as far as needed.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"TD" wrote:

I have a table as below:

A B C D E
1 Value 2 9 5 Total
2 Item 1 x
3 Item 2 x x
4 Item 3 x
5 Item 4

Whereever x is plotted, I need the cell to be assigned values from the
corresponding row above.
Eg. Item 2 is marked x in col B, I want the cell B3 to have a value 2 even
though x can be seen. Similarly item 2 is marked x in Col D, should now have
a value 5.
I need to sum up these values in the total column E.

Do reply.

Substitute text with assigned values
 

If you want a result like the example below, you can use this formula
starting in E2 and copying down.

=SUMPRODUCT((B2:D2="x")*$B$1:$D$1)

A B C D E
1 Value 2 9 5 Total
2 Item 1 x 9
3 Item 2 x x 7
4 Item 3 x 9
5 Item 4 x 2
6 Item 5 x x 11

If you want to actually change the values of the cells with x's to the
appropriate number, you will need a macro. Why don't you ask in the
Excel-Programming forum?