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 -
|