Referencing other cells
 

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.

Referencing other cells
 

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.

Referencing other cells
 

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 -

Referencing other cells
 

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 -

Referencing other cells
 

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 -