Confused with multiple 'IF' formula

[Durfun]

Hi everyone.

I'm trying to create a budget expenditure worksheet where values are dependent on items in a pick list/drop down list.

In worksheet #2 I've got items in col A each with an associated value ($) in column B.

In worksheet #1 I've got: 1) A drop down list for items (A), 2) a drop down list of value (B) and 3) a free text 'cost $' column C.

I'd like to have column D where it calculates (worksheet #2 (B) minus worksheet #1 column (C).

The difficulty I'm having is how to create a formula that says ' IF A1 = "X" THEN B1 = "X" AND D1=(B1-C1)

thanks

 

[Special-K99]

1. In Sheet 2 if you have a value in column B associated with that item why do you need a drop down list (column B) of the value in Sheet1?
Surely this is a simple lookup, given the item in Sheet1 column A, lookup the item in Sheet2 and return the value in Sheet2 column B

2. Sheet 1 column C is "Free text" saying "Cost $"
So it's text.
You cant perform a mathematical calculation on text (unless the text comprises wholly of a number).
10 in Column C is fine. B1-C1, 40-10 is 30
40 -"Cost $30" will produce an error.


Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html