Automatically insert lines?
 

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.

Automatically insert lines?
 

Hi KJ,

If you are using Excel 2003 select the data input area with titles and press
Ctrl+L, OK. If you are using Excel 2007 you can also press Ctrl+T, OK. You
are creating a list or table which will address your first issue. When you
complete the last entry on the new line press Tab.

To solve the second issue you may need to use VBA, here is a sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A2"), Target)
If Not isect Is Nothing Then
Range("A2:C2").Insert Shift:=xlDown
End If
Application.EnableEvents = True
End Sub

--
Cheers,
Shane Devenshire


"KJ" wrote:

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.

Automatically insert lines?
 

Shane Part one is awesome!! Exactly what I was looking for. Part Two I'm
going to live with. I don't VBA well enough to dabble like that. But thank
you very much!!

"ShaneDevenshire" wrote:

Hi KJ,

If you are using Excel 2003 select the data input area with titles and press
Ctrl+L, OK. If you are using Excel 2007 you can also press Ctrl+T, OK. You
are creating a list or table which will address your first issue. When you
complete the last entry on the new line press Tab.

To solve the second issue you may need to use VBA, here is a sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A2"), Target)
If Not isect Is Nothing Then
Range("A2:C2").Insert Shift:=xlDown
End If
Application.EnableEvents = True
End Sub

--
Cheers,
Shane Devenshire


"KJ" wrote:

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.

Automatically insert lines?
 

Glad to help.
--
Thanks,
Shane Devenshire


"KJ" wrote:

Shane Part one is awesome!! Exactly what I was looking for. Part Two I'm
going to live with. I don't VBA well enough to dabble like that. But thank
you very much!!

"ShaneDevenshire" wrote:

Hi KJ,

If you are using Excel 2003 select the data input area with titles and press
Ctrl+L, OK. If you are using Excel 2007 you can also press Ctrl+T, OK. You
are creating a list or table which will address your first issue. When you
complete the last entry on the new line press Tab.

To solve the second issue you may need to use VBA, here is a sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A2"), Target)
If Not isect Is Nothing Then
Range("A2:C2").Insert Shift:=xlDown
End If
Application.EnableEvents = True
End Sub

--
Cheers,
Shane Devenshire


"KJ" wrote:

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.

Automatically insert lines?
 

ok, quick follow up:
I'm running Excel 07. Some people in my office are still running 2000 and I
see this doesn't work on their version. Is there anything for them that
would do the same thing?

"ShaneDevenshire" wrote:

Glad to help.
--
Thanks,
Shane Devenshire


"KJ" wrote:

Shane Part one is awesome!! Exactly what I was looking for. Part Two I'm
going to live with. I don't VBA well enough to dabble like that. But thank
you very much!!

"ShaneDevenshire" wrote:

Hi KJ,

If you are using Excel 2003 select the data input area with titles and press
Ctrl+L, OK. If you are using Excel 2007 you can also press Ctrl+T, OK. You
are creating a list or table which will address your first issue. When you
complete the last entry on the new line press Tab.

To solve the second issue you may need to use VBA, here is a sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A2"), Target)
If Not isect Is Nothing Then
Range("A2:C2").Insert Shift:=xlDown
End If
Application.EnableEvents = True
End Sub

--
Cheers,
Shane Devenshire


"KJ" wrote:

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.

Automatically insert lines?
 

Hi KJ,

Microsoft introduced the List feature in 2003 and its equivalent Table in
2007, so your people are using 2002 or 2000 these techniques don't work. I
believe your only solution for them would be more VBA but it didn't sound
like that was a very viable idea.

--
Sorry,
Shane Devenshire


"KJ" wrote:

ok, quick follow up:
I'm running Excel 07. Some people in my office are still running 2000 and I
see this doesn't work on their version. Is there anything for them that
would do the same thing?

"ShaneDevenshire" wrote:

Glad to help.
--
Thanks,
Shane Devenshire


"KJ" wrote:

Shane Part one is awesome!! Exactly what I was looking for. Part Two I'm
going to live with. I don't VBA well enough to dabble like that. But thank
you very much!!

"ShaneDevenshire" wrote:

Hi KJ,

If you are using Excel 2003 select the data input area with titles and press
Ctrl+L, OK. If you are using Excel 2007 you can also press Ctrl+T, OK. You
are creating a list or table which will address your first issue. When you
complete the last entry on the new line press Tab.

To solve the second issue you may need to use VBA, here is a sample:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A2"), Target)
If Not isect Is Nothing Then
Range("A2:C2").Insert Shift:=xlDown
End If
Application.EnableEvents = True
End Sub

--
Cheers,
Shane Devenshire


"KJ" wrote:

OK brainstormers.
I'll simplify what I'm trying... Picture a quote sheet created. The line
item portion of the quote would lie from A1 to B3. (Quantity, Item, Price)
It would total in row C (C3).

I enter my info on row A and hit enter I automatically go to row B. Is there
a function I'm missing where after I enter the last item on row B (Price)
that I would automatically get another row inserted, as if I had more items
to quote?
I've seen a function like this in databases, but I need to keep this
function a part of Excel.

Part Two. Again I've seen this in databases but need to keep in Excel. To
keep track of my quotes is there a way so that the first row is blank. As I
enter my customer name and total price that it would automatically move it to
row B and again row A would be blank?
I'm not an experience code writer so if VBA is my only option just tell me
I'm SOL!
Thanks to one and all.