What is the best way to do it...?

[917]

Hi all

I've got a workbook that has two tabs:

1. Values
2. Answers​

The 'Values' tab has two columns

​

A. Criteria
B. Rate - different for each row, max 4 rows (B1 to B4)​

The 'Answers' tab has three columns

A. Help needed
B. How often
C. Cost​

At the moment, it simply uses the sum =A1*B1*Values!B1 (through to C20) to return the cost.

I need to find a way that allows the user to determine on the 'Answers' tab, what rate should be used to perform the sum and return the cost.

Is an IF statement the way to go (looking up say Yes in a cell and then calculating) or am I best using VBA somehow?

I was trying to figure it out using four cells, where the user enters a Yes in one of them, with the sum for 'Answers' column C as:

=IF(E1="Yes",A1*B1*Values!A1),IF(E2="Yes",A1*B1*Values!A2),(E3="Yes",A1*B1*Values!A3),(E1="Yes",A1*B1*Values!A4)

But I think that's just poor form lol.

Any pointers or code gratefully received.
Many thanks

 

[cyrilbrd]

Hi,
What happens if the answer is 'no'?
What is the condition for the criteria?

 

[917]

Hi cyrilbrd

Thanks for the reply, I ended up finding a whole new way.