Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

formula help

Status
Not open for further replies.

OCM

MIS
Sep 12, 2002
222
US
Greetings,
I've the following formula which partially works, but for some reasons, gives me a 0 instead of the expected values.

=IF(D2="STANDARD_RELEASE",Sheet1!D2,Sheet2!B2)

The formula will look in O.Type (column D) of sheet2 to check value is “STANDARD_RELEASE” if so, then it will find the order # in sheet1 and overwrite it with the voucher #.
Otherwise, it will keep the original order #.

TIA,
Regards,



OCM
 
Hi,

What column is the formula in?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Hi Skip,

I inserted a new column (Column A) to input the formula. The original Order # is now in column B.

Thanks,

OCM
 
...and where are we on the original question? We are not mind readers. [ponder]

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Odds are the referenced cells are blank, which is why you're getting a 0. I suggest you go to the cells that are giving you a 0 and click the fx button to the left of the formula bar. It will open up the Function Arguments and show you what is happening with your logic statement and why you're getting your results.
 
Thanks for the reply.
This leads me to think the logic of my formula is not correct.

What I really want is the following:

In sheet2 look in (column D) if the value is STANDARD_RELEASE take its Order# look this Order# in sheet1 (column A) and the value in voucher# (column D) and overwrite sheet2 Order# with this voucher#.
e.g.
sheet2:
order# = 162346
O. Type = STANDARD_RELEASE
Sheet1:
order# = 162346
.
.
.
voucher# = 161883
Then original order# in sheet2 162346 will be overwritten by the 161883 voucher#


Please let me know if this clarifies.

TIA,


OCM
 
What you want to be done needs to be in a macro. Basic Excel functions will not overwrite data in a cell. If you don't want a macro, what you can do is have the IF statement in another cell (have the column heading "Revised Order #" or something). Unless the data in Sheet1 matches the date in Sheet2 (i.e., the data in row 2 in Sheet1 & Sheet2 refer to the same orders), you'll probably have to use MATHC & INDEX or VLOOKUP functions to get the data you want.
 
Zelgar,

Yes, I created a new column named it ‘modified order #’ and that is where I’d my formulas.
If possible, I prefer MATCH, INDEX, VLOOOKUP etc Any idea?

Thanks,


OCM
 
Your original approch assumes that the data on sheet 1 has a row to row direct association with the data on sheet 2. That seldom happens. It's more likely that the rows are not in synch and that you'll need a lookup approch.

So we need more information about how your tables are structured and what it is you're trying to accomplish.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 

Hi guys,

Thank you for the suggestions. The following formula (Column A) gave me the correct values.

=IF($D2="STANDARD_RELEASE",INDEX(Sheet1!$D:$D,MATCH($B2,Sheet1!$A:$A,0)),$B2)

Regards,

OCM
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top