Enter same data in 3 rows

[mrdata]

I need to enter the same data three times in three differant rows .

Example:

As I type in column A:F Row 1
I want the same data to appear in the next 2 rows automaticly
I know excell repeats some values as you type but I want to eliminate having to type three rows of the same data for each entry I have to make

Can this be done?

Thanks
Charles

[Bryan Hessey]

I need to enter the same data three times in three differant rows .

Example:

As I type in column A:F Row 1
I want the same data to appear in the next 2 rows automaticly
I know excell repeats some values as you type but I want to eliminate having to type three rows of the same data for each entry I have to make

Can this be done?

Thanks
Charles

Put the formula

=IF(OR(OFFSET($A$1,ROW()-2,COLUMN()-1)="",ISBLANK(OFFSET($A$1,ROW()-2,COLUMN()-1))),"",OFFSET($A$1,ROW()-2,COLUMN()-1))

in any cell and formula fill sideways/downwards as required

hth
--

[mrdata]

In order to remove just one of the three entries the formula will be erased
I might be able to find a way around this with code to replace the formula.

Any suggestions on how to overcome this?
Also why do the copied rows have a differant font size than the typed row?

The typed row if font size 10
The two copied rows are font size 8


Thanks
Charles

[Bryan Hessey]

In order to remove just one of the three entries the formula will be erased

Yes, you could have a problem removing just the first of the three entries, but for the other two the formula can be overtyped etc, To restore it just drag the formula from any adjacent cell

I might be able to find a way around this with code to replace the formula.

'Del' and 'Formula Fill' are already built-in.

Any suggestions on how to overcome this?

Your question was how to replicate a row to the following two rows, what do you want to overcome? the replication can be overcome by omitting the formula.

Also why do the copied rows have a differant font size than the typed row?

The typed row if font size 10
The two copied rows are font size 8


Thanks
Charles

The formula copies cell contents only. My default font size is 10.

Did you Copy and Paste from your web browser? - and is the font size there 8? If you type the formula, or copy it from browser to Notepad and from Notepad to Excel does your problem disappear?

---

[mrdata]

How can I copy this formula down for many rows and have multiple sections of three rows that will do the replication.

example:
I have the fith row backcolored yellow to signify this is a row to be typed in.
Now I backcolor the 8th row then the 11th and so on I start at the 4th because I use the first 4 rows for header and other things.

Problem when I copy the formula and drag it down the absoulute cell references do not change in the formula.
I was hoping they would then I would only have to backcolor every 3rd row and earse the formula from that row thus leaving several sets of replication rows.

I need many sets of these
And the only way so far is to manualy change the cell references in each formula to replecate the typed in cells (Backcolored Yellow).
This could take forever.
Is there a simpler solution?
I mean the formula works great for the first set of three rows after that every cell fromula will have to be edited.

Thanks
Charles

[Bryan Hessey]

How can I copy this formula down for many rows and have multiple sections of three rows that will do the replication.

example:
I have the fith row backcolored yellow to signify this is a row to be typed in.
Now I backcolor the 8th row then the 11th and so on I start at the 4th because I use the first 4 rows for header and other things.

Problem when I copy the formula and drag it down the absoulute cell references do not change in the formula.
I was hoping they would then I would only have to backcolor every 3rd row and earse the formula from that row thus leaving several sets of replication rows.

I need many sets of these
And the only way so far is to manualy change the cell references in each formula to replecate the typed in cells (Backcolored Yellow).
This could take forever.
Is there a simpler solution?
I mean the formula works great for the first set of three rows after that every cell fromula will have to be edited.

Thanks
Charles

It really has no 'addresses' to speak of, the formula can be entered and copied to/from ANY cell

Easiest short of VB code is Either
Drag the formula and (bulk) Select- 'del' & colour every third row
or
set up a set of three, copy-paste-paste-paste then copy paste a set of 12 etc

do NOT change the cell ref from $A$1 - just copy that to anywhere (row 1 excluded).

----

[Mallycat]

I want to eliminate having to type three rows of the same data for each entry I have to make

Can this be done?

Thanks
Charles

Have you considered highlighting the 3 cells where you want to add the data, type the data once, then press Ctrl Enter instead of Enter. This will put the same data in all 3 cells.

Matt

[mrdata]

That works man thanks a lot
If I have anymore problems with it I will post.

Thanks again
Charles

[Bryan Hessey]

That works man thanks a lot
If I have anymore problems with it I will post.

Thanks again
Charles

Good to see, and thanks for the response.

[mrdata]

I have a macro that needs to run and filter through the data and retrieve the data I want.

I need to lock the cells that have the formula's I know how to do that manualy.

But the macro won't run with the worksheet protected it error's out!

Is there a way to unlock the sheet run the code then re-lock the sheet with a line of code added to the macro?

If so what would that code be?

Or is there another way to do it?

Thanks
Charles

[Bryan Hessey]

I have a macro that needs to run and filter through the data and retrieve the data I want.

I need to lock the cells that have the formula's I know how to do that manualy.

But the macro won't run with the worksheet protected it error's out!

Is there a way to unlock the sheet run the code then re-lock the sheet with a line of code added to the macro?

If so what would that code be?

Or is there another way to do it?

Thanks
Charles

Just record a macro whilst Unprotecting, select a cell (ie do nothing marker) then Protect the sheet again. Stop recording, then transfer that code, in two parts, to the start and end of your macro.

hth
---

[mrdata]

I am using this code to force the cells to be uppercase it works but runs slowly I think it is calculating the entire workbook any Ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
On Error GoTo Fixit:
Set MyRange = Range("B5:D2080")
If Application.Intersect(Target, MyRange) Is Nothing Then Exit Sub

With Target
If Not .HasFormula Then .Value = UCase(.Value)
End With

Fixit: Exit Sub

End Sub

Thanks
Charles

[Dav]

it runs fine with me, if you have a lot of formulas are they recalculting when the values are changed, hence making it seem to slowly. As a bit of code it is quick

Regards

dav

[Mallycat]

it runs fine with me, if you have a lot of formulas are they recalculting when the values are changed, hence making it seem to slowly. As a bit of code it is quick

Regards

dav

I concur with the above

[mrdata]

The problem is fixed using this code

Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D5:D3000")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True

Thanks
Charles