Author Topic: Excel Help  (Read 3654 times)

Offline [BTF]adam

  • Brobdingnagian Member
  • ***
  • Posts: 4047
    • View Profile
    • adam.tastyspleen.net
  • Rated:
Excel Help
« on: November 16, 2010, 02:51:14 PM »
soup,

I have been asked to make a stock control sheet for a family friend, it isnt overly complex but basically I need the following features and my excel is very rusty, so here's what im struggling with atm:


   Stock   Price   Number Required   Total
Paints            
Cadmium Red      £4.88      £0.00
Permanent Magenta      £3.67      £0.00
Alizarin Crimson      £3.25      £0.00
Cadmium Yellow      £4.88      £0.00
Lemon Yellow      £4.50      £0.00
Auriolin      £4.88      £0.00
Indian Yellow      £3.25      £0.00
Cadmium Orange      £4.88      £0.00
Green Gold      £3.57      £0.00
French Ultramarine      £3.57      £0.00
Cobalt Blue      £4.88      £0.00
Cerulean Blue      £3.67      £0.00
Quinacridone Gold      £3.67      £0.00
Olive Green      £3.25      £0.00
Burnt Umber      £3.25      £0.00
Raw Umber      £3.25      £0.00
Raw Sienna      £3.25      £0.00
Burnt Sienna      £3.25      £0.00

that is going to be formatted terribly im sure, but I hope you can see what I mean at least with the columns and rows.

what I mainly need atm is a way to, when the stock is filled in (say with 6 in each stock cell), and someone comes to my dude and buys say, 5 tubes of cerulean blue. well I've got the functions for totalling it up and stuff, but when my dude puts 5 in the Number Required box, I need the following to happen:

a) takes away whatever was just put into the number required box from the stock box. I think that is fairly simple, =SUM(norequiredcell)-(stock) if I am correct?

but then b) throws up a message box when say, the stock goes under 2 to remind him to restock this paint.

actually I just tried that (a) and it comes up with an error, I suppose it cannot calculate it like I want it to every time he puts a number in the "no wanted" box. I could make a stock and stock remaining box? but then that sort of defeats the purpose i guess? bleh i dunno
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus

Offline [BTF]adam

  • Brobdingnagian Member
  • ***
  • Posts: 4047
    • View Profile
    • adam.tastyspleen.net
  • Rated:
Re: Excel Help
« Reply #1 on: November 16, 2010, 03:03:03 PM »
after googlin a bit I came up with this off a yahoo answer thing


Private Sub Worksheet_Calculate()
If Range("A1") = "Available" Then MsgBox "Cell A1 indicates Available"
End Sub


could I modify that vb macro perhaps? maybe something along the lines of



Private Sub Worksheet_Calculate()
If Stock Left("E1") = "<2" Then MsgBox "This paint has dropped below 2 in stock, you need to restock the shelf!"
End Sub
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus

Offline Whirlingdervish

  • Super ShortBus Extravaganza
  • Illimitable Sesquipedalian Member
  • *
  • Posts: 6384
    • View Profile
    • The Dervish Depository
  • Rated:
Re: Excel Help
« Reply #2 on: November 16, 2010, 03:43:42 PM »
your best bet is making vb macros to do that.
you would want to do your comparisons correctly tho:


If Stock Left("E1") = "<2" Then

!=

If Stock Left(E1) < 2 Then



you wouldn't want to compare an integer or decimal value to a string or you'll get all sorts of funky shit going on.


  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus

Offline [BTF]adam

  • Brobdingnagian Member
  • ***
  • Posts: 4047
    • View Profile
    • adam.tastyspleen.net
  • Rated:
Re: Excel Help
« Reply #3 on: November 16, 2010, 05:18:15 PM »
fuck I hate VBA

Allow me to lay out exactly what I require because after a while fiddling I still can't get this to work, I am not even sure how to implement it.

On the cells F3-F20 (and more but for the purposes of right now), when the number in those cells drops below 2, I need a simple MsgBox to pop up with "Restock this paint".

In cells F3-F20 there is a SUM which minuses the "Number Required" cell with the "Stock" cell to let the user know the "Stock Remaining (F3-F20)"

Now, I  presume I need a Worksheet_Change thing.. or something. Everything I have come up with gives me a syntax or compile error.

Best I have come up with is something like

Sub Worksheet_Change()
If F3 - F20 = < 2 Then MsgBox "Restock This Paint"
End Sub

when I run this it runs without a problem, it is saved as a macro called "restock" in my workbook but nothing happens if I change the corresponding cells that makes the stock remaining cell go to 1 left.


  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus

Offline reaper

  • Opulent Member
  • *
  • Posts: 2872
  • Nice night for a walk, eh? - Nice night for a walk
    • View Profile
  • Rated:
Re: Excel Help
« Reply #4 on: November 16, 2010, 06:56:46 PM »
Usually I like to do shit in unixy land, but it's funny when someone grabs excel, makes a couple mouse moves, and magically they got what they want.

Sorry don't know Excel.  Why is the value of cell remaining moving over to the left.  Sholdn't it put columns, 1,2,3,4,5.  Maybe if it has to move you can reference a cell by the distance from the other cell.
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus
VaeVictus "reaper is a lying sack of shit and ragequit then had, probably slugs, come alias and beat me, wasnt even the same person playing OBVIOUSLY, accuracies basicly doubled, and strategy

Offline [BTF]adam

  • Brobdingnagian Member
  • ***
  • Posts: 4047
    • View Profile
    • adam.tastyspleen.net
  • Rated:
Re: Excel Help
« Reply #5 on: November 22, 2010, 11:21:25 AM »
in the end what seemed so simple ended up not so simple :P had to ask an excel help board and what worked eventually was the following:

Code: [Select]
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell        As Range
    Dim rInt        As Range
    Dim rOut        As Range

    Set rInt = Intersect(Target, Range("B3:B24"))
    If rInt Is Nothing Then Exit Sub

    For Each cell In rInt.Cells
        If cell.Value < 2 Then
            If rOut Is Nothing Then Set rOut = cell
            Set rOut = Union(rOut, cell)
        End If
    Next cell

    If Not rOut Is Nothing Then
        rOut.Select
        MsgBox "Restock selected item(s)"
    End If
End Sub
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus

Offline peewee_RotA

  • Brobdingnagian Member
  • ***
  • Posts: 4152
  • Hi, I'm from the gov'ment and I'm here to help you
    • View Profile
  • Rated:
Re: Excel Help
« Reply #6 on: November 25, 2010, 09:48:37 AM »
I think that I'm pretty savvy when it comes to Microsoft Word Documents. However, when it comes to spreadsheets, I have never really excelled.
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus
GOTO ROTAMODS (rocketgib)
GOTO ROTAMAPS (fireworks)
HappyFriar- q2server.fuzzylogicinc.com
 Tune in to the Tastycast!!!!  http://dna.zeliepa.net

Offline yahoo

  • Opulent Member
  • *
  • Posts: 2291
  • Quake II | Powered by SMF 1.0.9.
    • View Profile
  • Rated:
Re: Excel Help
« Reply #7 on: November 25, 2010, 03:37:32 PM »
I think that I'm pretty savvy when it comes to Microsoft Word Documents. However, when it comes to spreadsheets, I have never really excelled.

same here. lol i even just learned recently how to link data from different sources. BTW im good in powerpoints coz i used to make porn pic sideshows way back on dialup years and video streaming was never heard of :P.
  • Insightful
    Informative
    Funny
    Nice Job / Good Work
    Rock On
    Flawless Logic
    Well-Reasoned Argument and/or Conclusion
    Demonstrates Exceptional Knowlege of the Game
    Appears Not to Comprehend Game Fundamentals
    Frag of the Week
    Frag Hall of Fame
    Jump of the Week
    Jump Hall of Fame
    Best Solution
    Wins The Internet
    Whoosh! You done missed the joke thar Cletus!
    Obvious Troll Is Obvious
    DO YOU EVEN LIFT?
    DEMO OR STFU
    Offtopic
    Flamebait
    Redundant
    Factually Challenged
    Preposterously Irrational Arguments
    Blindingly Obvious Logical Fallacies
    Absurd Misconstrual of Scientific Principles or Evidence
    Amazing Conspiracy Theory Bro
    Racist Ignoramus
Quake II | Powered by SMF 1.0.9.
© 2001-2005, Lewis Media. All Rights Reserved.

 

El Box de Shoutamente

Last 10 Shouts:

 

|iR|Focalor

July 04, 2025, 06:33:05 AM
 

RyU

June 29, 2025, 06:27:46 PM
Q2 must never die  :)
 

|iR|Focalor

May 26, 2025, 01:17:30 PM
 

-Unh0ly-

May 22, 2025, 05:45:28 PM
 

Yotematoi

May 17, 2025, 08:33:15 AM
Yo desde el año 2007 me enfermé de Q2, es incurable  
Morir y revivir es costumbre, lástima q el QT estaba bueno
 

ImperiusDamian

May 12, 2025, 01:45:35 AM
Quake II is not 27 years old. I refuse to accept THAT much time has gone by.
 

|iR|Focalor

May 11, 2025, 02:33:39 PM
 

-Unh0ly-

May 03, 2025, 08:02:46 AM
198.179.6.200:30634
 

Yotematoi

May 02, 2025, 10:27:47 AM
Note for Player-4109 "Remember, the Guinness Book of Records does not include cowards who shot at someone by treachery." :D

Show 50 latest
Welcome, Guest. Please login or register.
July 05, 2025, 01:14:11 PM

Login with username, password and session length