Hello,

Let me start by saying that I am not an Excel person. I am a
programmer, I know many languages, but I have never dabbled seriously
in Excel. With that said, a client recently asked for better way to
calculate his prices and a complete database-backed web application
would be overkill, so I want to try and offer an Excel solution.

Here's what I would like to do. The client makes all sorts of
pavements. He has a rate for jobs under 400 square feet and a another
rate for jobs above 400 square feet. I would like to be able to
define the types of pavements he does, along with their rates in a
Worksheet like so (I'm using the pipe '|' character to show the
different cells):

Pavement foo | 16 | 14
Pavement bar | 16 | 14
Pavement baz | 17 | 15

In the "main" worksheet, the client would choose the pavement he wants
(with a drop down list from Data - Validations), he would input the
number of square feet and Excel should return the total cost.
Example:

Pavement bar | 800 | =IF(A2 <= 400; REF(OtherSheet!
Column1LineOfPavement bar); REF(OtherSheet!Column2LineOfPavement bar))

Is that kind of manipulation possible?

Thanks,

Vincent.

You need to use the VLOOKUP function. Assume the pavement type is
chosen in A1 and the area in B1 - enter this formula in C1:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))

where I have assumed the data on the OtherSheet occupies A1 to C20 -
adjust to suit.

The 3rd parameter of the VLOOKUP determines which column of the range
you want the matching data to be returned from - either column 3 or
column 2 in this case.

Hope this helps.

Pete

On Apr 30, 4:49 pm, vfoley wrote:
Hello,

Let me start by saying that I am not an Excel person. I am a
programmer, I know many languages, but I have never dabbled seriously
in Excel. With that said, a client recently asked for better way to
calculate his prices and a complete database-backed web application
would be overkill, so I want to try and offer an Excel solution.

Here's what I would like to do. The client makes all sorts of
pavements. He has a rate for jobs under 400 square feet and a another
rate for jobs above 400 square feet. I would like to be able to
define the types of pavements he does, along with their rates in a
Worksheet like so (I'm using the pipe '|' character to show the
different cells):

Pavement foo | 16 | 14
Pavement bar | 16 | 14
Pavement baz | 17 | 15

In the "main" worksheet, the client would choose the pavement he wants
(with a drop down list from Data - Validations), he would input the
number of square feet and Excel should return the total cost.
Example:

Pavement bar | 800 | =IF(A2 <= 400; REF(OtherSheet!
Column1LineOfPavement bar); REF(OtherSheet!Column2LineOfPavement bar))

Is that kind of manipulation possible?

Thanks,

Vincent.

I've just noticed that you asked for the total cost - my formula will
return the rate in C1, so you will need to multiply by B1 to get the
total cost. You could either do this in a separate cell (eg D1:
=C1*B1), or by modifying the formula:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))*B1

Hope this helps.

Pete

On Apr 30, 5:10 pm, Pete_UK wrote:
You need to use the VLOOKUP function. Assume the pavement type is
chosen in A1 and the area in B1 - enter this formula in C1:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))

where I have assumed the data on the OtherSheet occupies A1 to C20 -
adjust to suit.

The 3rd parameter of the VLOOKUP determines which column of the range
you want the matching data to be returned from - either column 3 or
column 2 in this case.

Hope this helps.

Pete

On Apr 30, 4:49 pm, vfoley wrote:



Hello,

Let me start by saying that I am not an Excel person. I am a
programmer, I know many languages, but I have never dabbled seriously
in Excel. With that said, a client recently asked for better way to
calculate his prices and a complete database-backed web application
would be overkill, so I want to try and offer an Excel solution.

Here's what I would like to do. The client makes all sorts of
pavements. He has a rate for jobs under 400 square feet and a another
rate for jobs above 400 square feet. I would like to be able to
define the types of pavements he does, along with their rates in a
Worksheet like so (I'm using the pipe '|' character to show the
different cells):

Pavement foo | 16 | 14
Pavement bar | 16 | 14
Pavement baz | 17 | 15

In the "main" worksheet, the client would choose the pavement he wants
(with a drop down list from Data - Validations), he would input the
number of square feet and Excel should return the total cost.
Example:

Pavement bar | 800 | =IF(A2 <= 400; REF(OtherSheet!
Column1LineOfPavement bar); REF(OtherSheet!Column2LineOfPavement bar))

Is that kind of manipulation possible?

Thanks,

Vincent.- Hide quoted text -

- Show quoted text -

Thank you very much, this was exactly what I needed.

Vincent

On Apr 30, 12:18 pm, Pete_UK wrote:
I've just noticed that you asked for the total cost - my formula will
return the rate in C1, so you will need to multiply by B1 to get the
total cost. You could either do this in a separate cell (eg D1:
=C1*B1), or by modifying the formula:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))*B1

Hope this helps.

Pete

On Apr 30, 5:10 pm, Pete_UK wrote:

You need to use the VLOOKUP function. Assume the pavement type is
chosen in A1 and the area in B1 - enter this formula in C1:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))

where I have assumed the data on the OtherSheet occupies A1 to C20 -
adjust to suit.

The 3rd parameter of the VLOOKUP determines which column of the range
you want the matching data to be returned from - either column 3 or
column 2 in this case.

Hope this helps.

Pete

On Apr 30, 4:49 pm, vfoley wrote:

Hello,

Let me start by saying that I am not an Excel person. I am a
programmer, I know many languages, but I have never dabbled seriously
in Excel. With that said, a client recently asked for better way to
calculate his prices and a complete database-backed web application
would be overkill, so I want to try and offer an Excel solution.

Here's what I would like to do. The client makes all sorts of
pavements. He has a rate for jobs under 400 square feet and a another
rate for jobs above 400 square feet. I would like to be able to
define the types of pavements he does, along with their rates in a
Worksheet like so (I'm using the pipe '|' character to show the
different cells):

Pavement foo | 16 | 14
Pavement bar | 16 | 14
Pavement baz | 17 | 15

In the "main" worksheet, the client would choose the pavement he wants
(with a drop down list from Data - Validations), he would input the
number of square feet and Excel should return the total cost.
Example:

Pavement bar | 800 | =IF(A2 <= 400; REF(OtherSheet!
Column1LineOfPavement bar); REF(OtherSheet!Column2LineOfPavement bar))

Is that kind of manipulation possible?

Thanks,

Vincent.- Hide quoted text -

- Show quoted text -

Thanks for feeding back, Vincent - glad to be of help.

Pete

On Apr 30, 5:27 pm, vfoley wrote:
Thank you very much, this was exactly what I needed.

Vincent

On Apr 30, 12:18 pm, Pete_UK wrote:



I've just noticed that you asked for the total cost - my formula will
return the rate in C1, so you will need to multiply by B1 to get the
total cost. You could either do this in a separate cell (eg D1:
=C1*B1), or by modifying the formula:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))*B1

Hope this helps.

Pete

On Apr 30, 5:10 pm, Pete_UK wrote:

You need to use the VLOOKUP function. Assume the pavement type is
chosen in A1 and the area in B1 - enter this formula in C1:

=IF(B1400,VLOOKUP(A1,OtherSheet!A1:C20,3,0),VLOOK UP(A1,OtherSheet!
A1:C20,2,0))

where I have assumed the data on the OtherSheet occupies A1 to C20 -
adjust to suit.

The 3rd parameter of the VLOOKUP determines which column of the range
you want the matching data to be returned from - either column 3 or
column 2 in this case.

Hope this helps.

Pete

On Apr 30, 4:49 pm, vfoley wrote:

Hello,

Let me start by saying that I am not an Excel person. I am a
programmer, I know many languages, but I have never dabbled seriously
in Excel. With that said, a client recently asked for better way to
calculate his prices and a complete database-backed web application
would be overkill, so I want to try and offer an Excel solution.

Here's what I would like to do. The client makes all sorts of
pavements. He has a rate for jobs under 400 square feet and a another
rate for jobs above 400 square feet. I would like to be able to
define the types of pavements he does, along with their rates in a
Worksheet like so (I'm using the pipe '|' character to show the
different cells):

Pavement foo | 16 | 14
Pavement bar | 16 | 14
Pavement baz | 17 | 15

In the "main" worksheet, the client would choose the pavement he wants
(with a drop down list from Data - Validations), he would input the
number of square feet and Excel should return the total cost.
Example:

Pavement bar | 800 | =IF(A2 <= 400; REF(OtherSheet!
Column1LineOfPavement bar); REF(OtherSheet!Column2LineOfPavement bar))

Is that kind of manipulation possible?

Thanks,

Vincent.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -