Posts by JohnnyBeGood


Hi,
I searched thru the forum but couldn't find an answer,
All I want is to copy values from ie. A1 B1 C1 D1 E1 F1.....
to column A ie
A1 A2 A3 A4 A5.....Thanks!


Re: Calculate Percentage Between Two Cells
Sorry, I expressed my self wrong here,
I'm looking for increase in percentage between 57 and 75, can't be 76% 
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

Re: Add formula output to same cell and get total
Problem solved with =A1*B1+A1
btw, B1 needs to be formated in percentage ie. 8.20% 
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: =A1A1/(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.16I just can't calculate total in one cell.

Re: Carry Over Data From Previous Row
I think so too, this will work,
after I copied formula down each column, I get the same
output in each column (see attached). 
Re: Carry Over Data From Previous Row
Again, thank you also for taking time to reply!
Quote from NinjaHi 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.Quote from Ninja
If the only reason for Quantity ch. is to show the initial stock (i.e. cell B3 in your latest example), then this field does not need to be separate from Carried over and you can just have 6 columns instead of 7:A1 Date Checked
B1 Initial Quantity (where B3 = E2)
C1 Date Used
D1 Quantity Used
E1 Quantity Left (where E2=IF(B2="",0,B2D2))
F1 Date ClearedAlternatively, if you want to be able to input additional quantity in Quantity ch. (something on top of what was carried over from the previous row), then you could retain the 7 column layout with something like this:
A1 Date Checked
B1 Quantity Ch.
C1 Carried Over (where C3 = F2)
D1 Date Used
E1 Quantity Used
F1 Quantity Left (where F2=IF(B2="",C2E2,B2+C2E2))
G1 Date ClearedLet me know if I have misenterpreted the purpose of Quantity ch. and Carried over
:ninja:
"I think" your alternate option is better,
please read my previous post. 
Re: Carry Over Data From Previous Row
First of all thank you for taking time to reply!
Quote from ByTheCringe2Well, 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 ByTheCringe2When items are used, the quantity in E is deducted from B, to give current stock F.
That's correct.Quote from ByTheCringe2How do you show new stock added?
In column B user enters a number manually.Quote from ByTheCringe2And 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.

Re: Carry Over Data From Previous Row
That works great!
now, sometimes when I enter something in B column "Quanitity ch" that is less then C column "Carried over"
in left I get 8, can this be fixed?Thanks!

Re: Carry Over Data From Previous Row
Quote from ByTheCringe2Can you attach a small sample workbook showing the new layout and the problem you have now, please?
Sure, I thought I'll try to explain but it gets way confusing 
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,B4D4F4H4J4) 
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? 
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.

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. 
Re: If No Data Leave It Blank (cell Contains Formula)
Quote from ByTheCringe2Sorry, try:
=IF(B2="","",IF(B2>TODAY(),B2TODAY(),"One time "&TODAY()B2&" Times"))
Thanks!
it works perfect.
problem solved : DOne 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,CodePrivate 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.

Re: If No Data Leave It Blank (cell Contains Formula)
I tried and I got
"The formula you typed contains an error"
for information about fixing common.......B2 contains date 03/31/2007 and is getting that date from this formula =A2 + 30

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(),B2TODAY(),"One time "&TODAY()B2&" Times")any idea guys?

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!