EXCEL PROBLEM

[gc1234]

Hi all,

I have 2 sheets, 1 sheet does a VLOOKUP in the 2 sheet and if found i need to reduce the total on that row by one? is this possible, its so i can track stock and know exactly what is or isnt in, by the way, I use the SKU code that lookups on on the first sheet.

many thxs

 

[SkipVought]

gc1234,

You can have a lookup formula that does the arithmetic, but it cannot change the ORIGINAL value. You'll need to do that with a marco.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

Well my lookup works fine,,,, doeS VLOOKUP have a return value that i could use so i can subtract on that row and then the by the column on that row with the amount of stock i have? just thinking on the fly here

 

[SkipVought]

If your lookup formula returns a numeric value you can use it in any arithmetic calculation you like

For instance
[tt]
=B1-IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$Z$9999,5,FALSE)),0,VLOOKUP(A1,Sheet2!$A$1:$Z$9999,5,FALSE))
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

not sure what you mean, I only use VLOOKUP so far which is fine,

I have 3 rows I'm concerned about

A is the column i enter an SKU number
B is a vlookup into sheet2 and does a match with A values is that sheet
C holds the box number which is also pouplated.. B & C work great,

D i would like to have the value in sheet 2 column D set to itself minus one.

I hope this helps

 

[SkipVought]

D i would like to have the value in sheet 2 column D set to itself minus one."

Based on what? Surely EVERY value is not -1???

What does the lookup return in column B?


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

B = CODE NUMBER, C = description

 

[SkipVought]

D cannot have BOTH a value AND a formula.

You can create a NEW column that performs a calculation on column D

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

That would do it, a d lookup formula to print the result of the second sheet - 1... any ideas. ... thxs for helping

 

[SkipVought]

as pervoiusly posted

For instance

=D1-IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$Z$9999,5,FALSE)),0,VLOOKUP(A1,Sheet2!$A$1:$Z$9999,5,FALSE))

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

Hi Skip, I think he's treating Sheet2 as an inventory sheet, listing available stock for each SKU, and then wants to decrement his available stock of any particular SKU on sheet2 by 1 for each instance of that SKU on his sheet1.

What is unclear to me though, I think, is whether he ever only has the one SKU on sheet1 that he is looking up, OR, whether Sheet1 is a complete list of all SKUs purchased.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

He's trying to write a database application with excel sheet functions -- not a good idea!

He will need to write VBA code to do this unless you want to do alot of post-process massaging -- VERY error prone approch!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

What would be nice is a macro.... I'll tell you what i have..

I have a barcode reader that scans the product code, this is then matched in the sheet using

=VLOOKUP(TEXT(B2,"0"),Sheet2!$A$1:$C$3995,2,FALSE)

for each item found... Using similar formula I get other information from sheet2.

I have a column in there called qty so it would be nice to decrease the qty if a product item was found.

Cheers GC1234

 

[SkipVought]

I'd suggest a button to run the update.

If B2 contains your SKU value then return the on hand quantity via
[tt]
C2: =MATCH(TEXT(B2,"0"),Sheet2!$A$1:$A$3995,0)
D2: =INDEX(Sheet2!$B$1:$B$3995,C2,1)
[/tt]
C2 is the relative row offset of the SKU recors on sheet2
D2 is the on hand quantity for the SKU
E2 will be where you enter the put or take quantity

Hit the button to update the record

Sub Button_Click()
  Sheet2.cells([C2], "B").Value = [D2] + [E2]
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

This looks good Skip... can you send us the spreadsheet example pls so i can see it work so i know what to do?

gsc1ugs@yahoo.co.uk


many thxs

 

[SkipVought]

gc,

As a programmer, you ought to be able to take what I have given you and at least START to set up the application.

Use the Control Toolbox Toolbar to place a Command Button on your sheet.

Right click the button and select view code. This is the code sheet for the Worksheet Object that you are currently working on.

Select CommandButton1 from the Objects drop down in the upper left of the code window.

Select the Click event from the Procedures drop down in the upper right of the code window.

This is where the code I gave you would go.

The hunt and find/failure and success will help you.

Post back with your specific questions.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gc1234]

If i had 2 items in sheet1 with value

716770009715

and this would valid number is sheet2 a1:a4000 how would the above be modified?

 

[gc1234]

Would this

=MATCH(TEXT(A1667,"0"),Sheet1!B1:B4000,0)

Lookat cell a1667 for a match with any cell in SHEET1!B1:B4000 ?

 

[KenWright]

Yes, but unless the data in B1:B4000 is text as well, then it will never find a match. As long as what you are searching is text then you should be OK. Careful now though - Given that the range you are searching starts on row 1, the value returned by the MATCH function will in fact be equal to the row value that that record sits on, BUT, it is not the row value that is being returned, it is simply the relative position in the range that is being returned, but because you started at one, that happens to coincide with the row value, eg:-

With "abc" in cell B4

=MATCH("abc",B1:B10,0) will return 4

but with "abc" still in B4

=MATCH("abc",B3:B10,0) will return 2

The only thing that changed was that I altered the beginning of the range being searched, and now cell B4 is the '2nd' cell in that range, hence the 2, whereas in the first formula it was the '4th' cell in the range hence the 4 - Nothing to do with the fact that it was actually on row 4.

Regards
Ken.................










----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[gc1234]

This

=MATCH(TEXT(A1667,"0"),TEXT(Sheet1!B13,"0"),0)

Gives me #n/a even though the cell values are the same? I know nothng about excel or the functions