if statement needed

L

lrbtav

Here is my scenario:

I have two workbooks in one xls file. Workbook 1 takes values from
Workbook 2 and formats them as a receipt. I have 10 products on
Workbook 2, each with a different name and a different price.

What I need for Workbook 1, is a way to:

1. detect when I have a value other than "0" in the quantity cell on
Workbook 2
2. If the quantity is >0, enter the quantity into a cell in Workbook 1
3. multiply the cell value that is the quantity by a designated price;
the price of the product.


How do I accomplish this?

Thanks.
 
R

Ragdyer

Assume qunatity is B2 on Sheet2,
Assume price is in C2 on Sheet2:

=IF(AND(Sheet2!B2<>0,Sheet2!C2<>0),Sheet2!B2*Sheet2!C2,"Need Data")
 
M

Maistrye

Here is my scenario:

I have two workbooks in one xls file. Workbook 1 takes values from
Workbook 2 and formats them as a receipt. I have 10 products on
Workbook 2, each with a different name and a different price.

What I need for Workbook 1, is a way to:

1. detect when I have a value other than "0" in the quantity cell on
Workbook 2
2. If the quantity is >0, enter the quantity into a cell in Workbook 1
3. multiply the cell value that is the quantity by a designated price;
the price of the product.


How do I accomplish this?

Thanks.
Click to expand...

Do you mean Two Worksheets in the same .xls file? Assuming this i
what you mean:

1. Should probably combine with #2.
2. In the cell in Workbook 1, put the formula =IF('Workboo
2'!A1>0,'Workbook 2'!A1,""). (Change A1 to wherever it is in Workboo
2)
3. In the cell in Workbook 1, you can put =IF('Workboo
2'!A1>0,'Workbook 2'!A1 * 'Workbook 2'!B1, ""). (Change A1 to whereve
the quantity is in Workbook 2, and the B1 to wherever the price is i
Workbook 2.)

Scot
 
L

lrbtav

Ragdyer ,

Thank you, that helps me out.

Another question: Let's say I have a 'quantity' column in Sheet2. I'm
looking at cell B2, which has a value of 3.

Is it possible, in Sheet1, to display the text "Red" in a certain cell
if the value of B2 in Sheet2 is greater than 0 (in this case; 3)?

Thanks again.
 
L

lrbtav

To clarify:

I have 42 different products, I only want those who have a quantity
greater than 1 to show up on the receipt sheet.

Hopefully this makes more sense; Thanks!
 
R

Ragdyer

You've now asked 2 different questions.

What happens if you take this formula and copy it down a column on Sheet1?

Does that do what you want?

Assume product name is in Column A of Sheet2:

=IF(Sheet2!B2>1,Sheet2!A2,"")
 
L

lrbtav

Sorry for the confusion. If I post your formula into Sheet1, it does
what it should; checks the value of the cell, and if greater than one,
it auto fills.

I have another question, now that we have that resolved.

Sheet1 has this function: =Sheet2!$J$2 - what this does, is allows me
to enter a 'row number' from Sheet2, which directs all other
functionson Sheet1 such as " =INDIRECT("Sheet2!AC" &RowIndex) " to use
all values in the corresponding row.

Problem: This series of formula works for my phone/fax number, but how
do I integrate it with your product/quantity formula?

(IE: I enter a row number, it pulls up the customer information as well
as their order quantities, currently, the order quantities stay the
same because the formula doesn't know which row to look in)

Thank you SO MUCH for your help!
 
L

lrbtav

=sheet1!$J$5 - on sheet;1 this formula is set to a cell, I enter a # in
this cell and it brings up customer information on sheet1; the cell is
'defined' with this formula. data is stored on sheet2

=INDIRECT("sheet2!AA" &RowIndex) - on sheet1; this formula grabs the
phone number after i enter a number into the first formula.

all other formulas are based on INDIRECT; after I enter a number in the
first formula, the values change on sheet1.


Thanks!
 
R

Ragdyer

Just put the row number in A1 of the sheet *this* formula is in:

=IF(AND(INDIRECT("Sheet2!B"&A1)<>0,INDIRECT("Sheet2!C"&A1)<>0),INDIRECT("Sheet2!B"&A1)*INDIRECT("Sheet2!C"&A1),"Need
Data")
 
L

lrbtav

Ragdyer ,

Thanks for the formula; I'm still having trouble though. I'm going to
link you with an example xls file I made so you can see what I'm
talking about. (no macros/viruses- dont worry)

http://www.geocities.com/nfl2k2/test2.xls

I would like to enter a number into the 'record' cell on the form sheet
and display colors with their related quantity, but only those greater
than 0, along with the name/address (which is working)

Does this make sense? Thanks again, I appreciate the help!
 
R

Ragdyer

Could you re-phrase exactly what you're looking to accomplish?

I don't understand what you mean by:

<<<"I would like to enter a number into the 'record' cell on the form sheet
and display colors with their related quantity">>>

Display colors where? ? ? In the record cell?

Their related quantity - Whose "their"?
And where are the colors "related"?
 
Top