Help!!!!

[MeAgain]

Hi,
I have sheet with the persons names and the papers they want to be delivered
from a-h columns. Now I do calculate it manually and enter the weekly toltal
in to column I.

I have these papers and their prices.
D. Express £0.35
D. Mail £0.40
D. Mirror £0.32
D. Telegraph £0.55
The Sentinel £0.30
and so on.

Could you help to make formula like (D.Express+D. Mail +Sentinel)= £1.05
sometimes the paper price change so i have to change about 230 cells
individually.
thanks

 

[MeAgain]

HI DAn,
I use the secound case. ie
Name | Papers | |
Bill | D.Mirror | D.Telegraph | =.55+.32 ( I have enterd them
manually)
Joe |D.Telegraph | TheSentinel | =.55+.30

The range containing prices are on the other sheet name Paper_Prices.
A B
1 D. Express £0.35
2 D. Mail £0.40
3 D. Mirror £0.32
4 D. Telegraph £0.55
5 The Sentinel £0.30

thanks

 

[Dan E]

MeAgain,

Here is my suggestion.

On the Paper_Prices sheet name the ranges containing the paper
prices with the appropriate paper name. ie. select cell A1 in your
example data, then "Insert" -> "Name" -> "Define". The problem
is that you can't include spaces so just put in "D.Express" with no
spaces (without quotes).

Now on the sheet containing the papers data put in this formula
to get the paper prices.
=INDIRECT(SUBSTITUTE(B2," ",""))
the substitute removes the spaces from the text
the indirect looks up the number in the named range.

Leave blank rows for the maximum number of papers (ie 6)
Then in the next column put (ie H)
=IF(B2<>"",INDIRECT(SUBSTITUTE(B2," ","")))
which avoids getting non summable values.
copy this formula over (6 cols) and down

Then in the last column (N) put
= SUM(H2:M2)

in this example columns H through M could be hidden if you
didn't want to see them.

This is sorta complicated, but if you would like I could send you
a sample sheet, or you could send me yours.

Dan E

 

[MeAgain]

Hi Dan I have sent you the sheet.
Please check your mail.
Thanks

 

[MeAgain]

Thanks Dan.
Thats fantastic. thats what I wanted VLookup.
Thanks for your time.
MeAgain