Excel Help

[sujyo]

Hello All... I need excel guru help...


Sheet1

COLUMN A COLUMN B

TODAY'S GAS PRICE NEW GAS PRICE

$3.559 3.699


Sheet2

COLUMN A COLUMN B

TODAY'S GAS PRICE NEW GAS PRICE

$3.699


Sheet3

COLUMN A COLUMN B

TODAY'S GAS PRICE NEW GAS PRICE

$3.699


In this Worksheet I have 1 to 31 sheets. I keep track our gas price in this worksheet daily manually. As shown above I need help to make a formula to A2 in sheet2 If sheet1 B2 (New gas Price) have new price otherwise A2(todays gas price)

will come automatic. If there is no price change in sheet1 B2 then Sheet1 A2 value goes to A2 in sheet2.If there is price change in sheet1 B2 then sheet1 B2 value goes to A2 in sheet2....waiting for your expert advise and thankyou in advance gurus...

 

[Hui]

I'm note exactly sure but I think you want something like:

In Sheet1!A2, =IF(B2>0,B2,Sheet2!B2)

 

[sujyo]

Thanks Hui for your reply and I am very sorry for late answer

Here let me explain again...


Sheet1 (9-1-11) has column P2 (today's gas price for exmp $3.699) and column Q2 (today's new gas price. if gas price change I enter new gas price in sheet1 Q2 for exmp $3.599. if gas price not change then Q2 is blank) gas price can go up or down.


Sheet2 (9-2-11) have same column P and Q as above.

I need your help to make a formula to get the value (gas price) from sheet1 P2 & Q2 in to Sheet2 P2, if gas price change into sheet1 Q2 ($3.599) then $3.599 goes to sheet2 P2 and If there is no change in gas price then value from sheet1 P2 (3.699)goes to sheet2 P2.


Sheet3 (9-3-11) value form sheet2 P2 & Q2 goes to sheet3 P2


I hope you understand better this time....waiting for your reply...thanks

 

[Hui]

If your sheets are called Sheet1, Sheet2 etc


On Sheet2!P2 try

=IF(LEN(INDIRECT("sheet"&RIGHT(CELL("filename"),1)-1&"!Q2")),INDIRECT("sheet"&RIGHT(CELL("filename"),1)-1&"!Q2"),INDIRECT("sheet"&RIGHT(CELL("filename"),1)-1&"!p2"))


you can copy this to subsequent sheets P2 and it works


If your sheets are called 9-3-11, 9-4-11 etc

I would suggest renaming the sheets to 9-2, 9-3, 9-4 etc

Then the above equation will also work


In either case you will need to have a cell somewhere with a =Rand() in it

This forces the spreadsheet to recalculate everytime the data changes

 

[sujyo]

I try but no luck... here is the link for workbook...


https://docs.google.com/leaf?id=0B_PXe7nQkdgWYzExYWZkYTEtNmFmYS00N2ZhLTlkODYtZmJhYmQ0NTIwYmNj&hl=en_US


Thanks for your reply