Posts by JohnnyBeGood

  • Calculate Percentage Between Two Cells

    Re: Calculate Percentage Between Two Cells



    Thank you guys!
    That's what I was looking for.


    Johnny

  • Calculate Percentage Between Two Cells

    Hi all!


    This should be pretty easy,
    in A1 I have 57 in B1 I have 75 so if I calculate percentage between those two I should get somewhere around 5% instead I'm getting 76%,
    what am I doing wrong, in C1 I have =A1/B1 and then formated under percentage.


    Thanks,


    Johnny

  • Add formula output to same cell and get total

    Hi,


    I found bellow example on the web,


    Enter sale price in cell A1: 100.00


    Enter VAT / Tax in cell A2: 18.00%



    Use the following function to calculate the net sale price: =A1/(100%+A2)


    The result: 84.75



    To calculate the VAT / Tax use the following function: =A1-A1/(100%+A2)


    The result: 15.25


    how do I calculate if ie.
    A1 = 115.68
    B1 = 8.2
    so total in B3 should be 125.16


    I just can't calculate total in one cell.

  • Carry Over Data From Previous Row

    Re: Carry Over Data From Previous Row


    Again, thank you also for taking time to reply!


    Quote from Ninja

    Hi there,


    What is the difference in the functionality of Quantity ch. and Carried over in the context of your work?


    Only reason why I created Carried over is because it
    would not fit on a page when it was printed.


    "I think" your alternate option is better,
    please read my previous post.

  • Carry Over Data From Previous Row

    Re: Carry Over Data From Previous Row


    First of all thank you for taking time to reply!


    Quote from ByTheCringe2

    Well, I'm not sure what you are doing. Can we go over it from scratch?


    Of course, I'm sure there's easier way to accomplish this.

    Quote from ByTheCringe2

    When items are used, the quantity in E is deducted from B, to give current stock F.


    That's correct.

    Quote from ByTheCringe2

    How do you show new stock added?


    In column B user enters a number manually.

    Quote from ByTheCringe2

    And I think you said earlier you can omit "Carried over" - can you?


    I thought I could but now I think its not possible.



    I'll give my best to explain the purpose of this spreadsheet,


    User is a installer of.... let's call it OzgridProdukt,
    So he goes to a warehouse today and takes 18 OzgridProdukt's
    He needs to enter in his spreadsheet that he took 18 OzgridProdukt's, later that day he goes
    To a field and installs 12 OzgridProdukt's for one day, so for that day after he comes back he needs
    To enter how many of them he used that day and how many he has left (in this example he has 6 left),


    Tomorrow he's ready to go out and install more but he has only 6 left and he has 9 work orders so he goes to warehouse
    and takes 3 more,


    at any time but usally at the end of day he can be asked how many he has left and how many he used today or
    In the past 2 days, that's why all of this information needs to be printer friendly and that's what makes it hard.


    I hope you guys understood what I'm trying to create here, new ideas are very welcomed.

  • Carry Over Data From Previous Row

    Re: Carry Over Data From Previous Row


    The reason why I recorder quantity changes is because every time when something was used (quantity change) user needs to fill out a paper and turn it in, so this keeps a track of when something was used and how many,


    I like your idea about rearrangement, its much more simple,
    now I have 7 columns (Date checked, Quantity ch, Carried over, Date used, Used, Left and Cleared on)
    as you can see I included "Carried over" column just after "Quantity ch",
    Now "Carried over" always includes some number but in "Left" column I get 0 ?
    "Left" column contains this formula = IF(B4="",0,B4-D4-F4-H4-J4)

  • Carry Over Data From Previous Row

    Re: Carry Over Data From Previous Row


    Thanks!
    Yes, that's pretty close to what I'm trying to achieve,
    but there some small bugs that I need to work out,


    If I enter your formula in B4 then I get error "#VALUE!" in L4
    that's because there's nothing to calculate,


    Under column A & B (Date Checked & Quantity Ch.) I meant
    the day something was taken out of inventory and quantity that was taken, having said that user can't always predict if previous row will be populated all the way (up to last cell ie. J4),
    so let's say user needs more items to take out some day he we'll need to leave a empty row in order everything to work,
    is there anyway around this?

  • Carry Over Data From Previous Row

    Hi,


    Attached is a sample of excel file that pretty much describes what I want to accomplish,


    its a simple spreadsheet that suppose to track a usage of items that were taken out of inventory,
    but at the same it needs to be printer friendly (that's what's causing all the problems),


    it got messy when I had to carry over from previous row,
    I had to manually enter under "Quantity ch." in order to have correct amount under "Left" column, (see row #4)


    I think I can eliminate whole "Carried over" column if I create a formula that will enter data into B4 only if J3 has a number, I also need to copy the same formula's down each column,


    Thanks.

  • If No Data Leave It Blank (cell Contains Formula)

    Re: If No Data Leave It Blank (cell Contains Formula)


    Ok, finally accomplished what I wanted :)
    maybe someone will find it useful in the future,


    to lock certain columns and leave others editable (in my example I wanted to leave A, B and C editable)
    I selected column A, B and C and then went to [COLOR="Red"]Format > Cell >
    Protection[/COLOR] unchecked locked and then went to [COLOR="Red"]Tools > Protection > Protect Sheet[/COLOR] and unchecked select locked cells and left check mark on select unlocked cells.

  • If No Data Leave It Blank (cell Contains Formula)

    Re: If No Data Leave It Blank (cell Contains Formula)


    Quote from ByTheCringe2

    Sorry, try:


    =IF(B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))


    Thanks!
    it works perfect.
    problem solved : D


    One more question,
    can I use lenze's code to protect column D, E and F from users trying to alter the outputs of formula's?
    and leave column A, B and C for users to change,

    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    If Target.Column <> 2 Then Exit Sub 
    If Target.Offset(0, -1) = "" Then 
        Cancel = True 
        Exit Sub 
    Else: Target = Target.Offset(0, -1) + 7 
    End If 
    Cancel = True 
End Sub


    I tried to alter it my self but couldn't figure out where in the code I can above columns.

  • If No Data Leave It Blank (cell Contains Formula)

    Re: If No Data Leave It Blank (cell Contains Formula)


    Thanks lenze!


    you saved me a lot of time, I didn't check my email so I didn't see your reply and I was trying everything but your tip :)


    I tried your Double_Click but that only forbids double click of selected cell and changing value, second one WorkSheet Change event I couldn't figure out what it does :(
    anyway, your first suggestion works perfect =If(A1="","",A1+7) with that I'm able to copy formula down the column without 01/07/1900 appearing in every copied cell,


    I was able to implement your fix into my other formula = TODAY()-A2 and it works perfect again =IF(A3="","",TODAY()-A3)


    but I can't implement into this
    =IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times")


    any idea guys?

  • If No Data Leave It Blank (cell Contains Formula)

    Re: If No Data Leave It Blank (cell Contains Formula)


    First of all thanks for a warm welcome!


    this solves my problem =If(A1="","",A1+7)
    Since I'm new to vba when I get home I'll try to figure out where to enter the other two codes and test how it works,
    attached is test spreadsheet in sheet1 is my problem in sheet2 problem is solved using above formula,
    I just need to figure out how to incorporate above in my other formulas,


    Thanks!