# Posts by JohnnyBeGood

• ## Copy Row Range To Column Range

Re: Copy Multiple Cells values Into One Column

Quote from Dave Hawley

Copy. Edit>Paste Special - Transpose

Yep, that's it.
Thanks!

• ## Copy Row Range To Column Range

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!

• ## 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

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%

• ## 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

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%

• ## 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

I think so too, this will work,
after I copied formula down each column, I get the same
output in each column (see attached).

• ## 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,

• ## 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

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!

• ## Carry Over Data From Previous Row

Re: Carry Over Data From Previous Row

Quote from ByTheCringe2

Can 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

• ## 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,

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)

I tried and I got
"The formula you typed contains an error"

B2 contains date 03/31/2007 and is getting that date from this formula =A2 + 30

• ## 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!