Author Topic: Excel Help  (Read 3466 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: 2290
  • 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:

 

Costigan_Q2

November 11, 2024, 06:41:06 AM
"Stay cozy folks.

Everything is gonna be fine."

There'll be no excuses for having TDS after January 20th, there'll be no excuses AT ALL!!!
 

|iR|Focalor

November 06, 2024, 03:28:50 AM
 

RailWolf

November 05, 2024, 03:13:44 PM
Nice :)

Tom Servo

November 04, 2024, 05:05:24 PM
The Joe Rogan Experience episode 223 that dropped a couple hours ago with Musk, they're talking about Quake lol.
 

Costigan_Q2

November 04, 2024, 03:37:55 PM
Stay cozy folks.

Everything is gonna be fine.
 

|iR|Focalor

October 31, 2024, 08:56:37 PM
 

Costigan_Q2

October 17, 2024, 06:31:53 PM
Not activated your account yet?

Activate it now! join in the fun!

Tom Servo

October 11, 2024, 03:35:36 PM
HAHAHAHAHAHA
 

|iR|Focalor

October 10, 2024, 12:19:41 PM
I don't worship the devil. Jesus is Lord, friend. He died for your sins. He will forgive you if you just ask.
 

rikwad

October 09, 2024, 07:57:21 PM
Sorry, I couldn't resist my inner asshole.

Show 50 latest
Welcome, Guest. Please login or register.
November 13, 2024, 10:08:55 PM

Login with username, password and session length