I have very big problem and I can't find answer for it. Maybe You can halp me.
1. In Sheet "Intro" I have row (5), in wich are formulas:
(i) C5(='Sheet1'!$A$1);
(ii) I5(=OFFSET('Sheet1'!$A$1;17;1;1;));
(iii) In this row also is number of my tables line, i. e B5(=1). This number "1" is also a hyperlink to Sheet1.
2. I have Macro, which every time automaticaly insert new row after last filled row, i. e. when I have filled 5 row and run my Macro, it inserts line 6. Therefore necessary to automatically fill this row with my formulas, that I mentioned above.
More specifically, the new row should be filled in like this:
When new row is 6:
(i) C6(='Sheet2'!$A$1);
(ii) I6(=OFFSET('Sheet2'!$A$1;17;1;1;));
(iii) B6(=2). This number "2" is also a hyperlink to Sheet2.
When new row is 7:
(i) C7(='Sheet3'!$A$1);
(ii) I7(=OFFSET('Sheet3'!$A$1;17;1;1;));
(iii) B7(=3). This number "3" is also a hyperlink to Sheet3.
When new row is 8:
(i) C8(='Sheet4'!$A$1);
(ii) I8(=OFFSET('Sheet4'!$A$1;17;1;1;));
(iii) B8(=4). This number "4" is also a hyperlink to Sheet4.
And......
Maybe You can help me?
In advance thank you sincerely.
Related:
Macro - Add row, auto fill text and formulas with relative OFFSS
Select a cell in the newly added row and run the following code:
Sub AddFormula()
Dim x As Integer
x = ActiveCell.Row
Range("B" & x).Value = x - 4
ActiveSheet.Hyperlinks.Add Range("B" & x), "", "Sheet" & x - 4 & "!A1"
Range("C" & x).Formula = "=Sheet" & x - 4 & "!$A$1"
Range("I" & x).Formula = "=OFFSET(Sheet" & x - 4 & "!$A$1,17,1,1)"
End Sub
But I have one more problem.
I have button, which instert date in the cell A1
I need, that in the cell a1 will be date of the time, when I pushed the button.
Now I use function =TODAY(), but this function is not suitable for me. He show actualy date every day, but not the date of the time, when I pushed the button...
Not clear to me what you mean by "Date of the time".
Do you want to display time only or date only or both?
Or do you mean that you want to keep the date and don't want it to change once it is entered?
It is not suitable for me.
I need the same effect like in function NOW or TODAY. I need fix the date, when row are inserting (date of insert). But in these functions (NOW or TODAY) is one wrong thing that Excel renews date each time I open the document.
Maybe do You have answer? Thank you
