Help anyone please - Format excel table with data

[joeyhl]

A	B	C	D
=sheet2 A2	=sheet2 B2	=sheet2 C2	=sheet2 D2
text	text	text	text
text	text	text	text
=sheet2 A3	=sheet2 B3	=sheet2 C3	=sheet2 D3
text	text	text	text
text	text	text	text
=sheet2 A4	=sheet2 B4	=sheet2 C4	=sheet2 D4
text	text	text	text
text	text	text	text

<tbody>
</tbody>

Hi guys,

I hope you could extend a little help. I've got two sheets in my workbook and what I want to do is to automate the "sheet1" upon encoding the data in "sheet2". However "sheet 1" is somehow formatted as above:

I intend to encode the data in another worksheet, "sheet2":

	A	B	C	D
1	123A	John	01022018	250
2	123B	Joseph	01032018	300
3	123C	Aaron	01042018	400
4	123D	Michael	01052018	150

<tbody>
</tbody>

Thank you in advance.

 

[Coding4Fun]

I think I might be missing what your problem is, but it sounds like you want Sheet1 to pull in the data from Sheet2 as it is updated.

What you have seems to be what you need but it looks like the range is off. In Sheet1 Cell A1 should be =Sheet2!A1 which would put "123A" FROM Sheet2 Cell A1 INTO Sheet1 Cell A1.

Please let me know if I am off on what you are trying to do.

 

[joeyhl]

Formula in a worksheet with certain format to reference cell in another worksheet

A	B	C	D
=sheet2 A2	=sheet2 B2	=sheet2 C2	=sheet2 D2
"text"	text	text	text
"text"	text	text	text
=sheet2 A3	=sheet2 B3	=sheet2 C3	=sheet2 D3
"text"	text	text	text
"text"	text	text	text
=sheet2 A4	=sheet2 B4	=sheet2 C4	=sheet2 D4
"text"	text	text	text
"text"	"text"	"text"	"text"

<tbody>
</tbody>

Thank you codingforfun. The excel table is actually formatted like above, it has some text descriptions, thus simply dragging the formula of reference cell (from sheet2) wont work as the text description skips two lines (shown above table). I hope I was able to explain what I need to happen.

 

[arunsjain]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

Hi Joeyhl,

Step 1: Create table and linked to other tab without "text" rows

Step 2: Insert blank rows manually or you can run following VBA code. Select range, key in interval of rows and key in how many blank rows you need.

You can also include following code in your personal macro book and can run on any worksheet to insert blank rows..

Sub InsertRowsAtIntervals()
    Dim rng As Range
    Dim xInterval As Integer
    Dim xRows As Integer
    Dim xRowsCount As Integer
    Dim xNum1 As Integer
    Dim xNum2 As Integer
    Dim WorkRng As Range
    Dim xWs As Worksheet
    Dim i
    
    If ActiveSheet.ProtectContents Then
    MsgBox "This worksheet is password protected." & vbCrLf & "Go to review tab and unprotect this worksheet.", vbOKOnly + vbInformation, "Protected Worksheet"
    Exit Sub
 
    End If
    
    Set WorkRng = Application.Selection
    On Error GoTo Endsub
    Set WorkRng = Application.InputBox("Range", "Select Range", WorkRng.Address, Type:=8)
    
    xRowsCount = WorkRng.Rows.Count
    
    xInterval = Application.InputBox("Enter row interval. ", "Rows interval", 1, Type:=1)
    
    xRows = Application.InputBox("How many rows to insert at each interval? ", "No. of blank rows", 1, Type:=1)
    
    xNum1 = WorkRng.Row + xInterval
    
    xNum2 = xRows + xInterval
    
    Set xWs = WorkRng.Parent
        
    Application.ScreenUpdating = False
    
        For i = 1 To Int(xRowsCount / xInterval)
            xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
            Application.Selection.EntireRow.Insert
            xNum1 = xNum1 + xNum2
        Next
    Application.ScreenUpdating = True
    
Endsub:
End Sub

Hope it will help.

Cheers!!

 

[joeyhl]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

Thanks arunsjain. I appreciate it. But is there a simple formula for excel? I dunno how to use vba either hehehe. I can't manually insert a table since my report is quite too long, from row 1 to about row 10,0000. And the same format as table above. Thus i want to encode in sheet2 the details containing the data under columns a2, a3, a4 and so on. but the formula in sheet1 should or will do the trick considering the "text rows". Simply dragging formula in row2 will result in ff; a2=sheet2 a2, and a5=sheet2 a6, and so on..which is not supposed to be.

 

[arunsjain]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

Other easy way to insert blank rows is:

Step 1: Create table and linked to other tab without "text" rows (Column B, C, D, E linked with other worksheet)

Step 2: In column A include series from 1 to 10,000 (like A2 to A10001 1 to 10,000).

Step 3: Copy this series two times after end of your last row in column A (like from A10002 to A20001 1 to 10,000, A20002 to A30001 1 to 10,000 ), as you need two blank rows between two rows.

Step 4: Filter entire table by Column A (Smallest to Largest value)

Now you will have two blank rows in between two rows which contains formula. You can delete Column A...

Hope this will help.

 

[joeyhl]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

thanks again arunsjain. will try this at home later. will let you know if i was able to do it.

 

[Coding4Fun]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

I did not understand your requirement apparently my apologies.

Reading through it appears that arunsjain is pointing you in the right direction.

 

[joeyhl]

Re: Formula in a worksheet with certain format to reference cell in another worksheet

Thanks a lot arunsjain, it worked! hehehe thank you really guys!
Thanks too Coding4Fun for your help, appreciate it.
God bless you all!