Dynamic Formulas with Dynamic Ranges

Is it possible to have Named dynamic ranges for a worksheet included in
formulas in another worksheet; but have the formulas automatically add rows
of more formulas for each row found in the source worksheet?

I find that I simply have to replicate formulas a good swath of rows to
ensure that all source data rows are calculated while using many IF
statements to prevent empty rows of the source data worksheet from appearing
as zeros in the formula worksheet.

I have variable rows on the source worksheet where the header row is on row
3 and the formula worksheet has the header row on row 1.

Hi

Formulas can't add any rows, they only can display a value in same cell they
are in - depending on parameters.

There is hard to advice anything with so sparse information - some example
of real data and formulas along with description of sheet design would be a
great help. But in general, usually there is some column in source workbook
having non-empty value whenever there are some data in row. Then you can
easily check it in target workbook. Something like this:
=IF(SourceSheet!A2="","",YourFormula)
When you have to check several cells, then something like:
=IF(AND(SourceSheet!A2="",SourceSheet!F2=""),"",YourFormula)
or
=IF(COUNTIF(SourceSheet!A2:C2,"<>")=0,"",YourFormula)
or
=IF(COUNTIF(SourceSheet!A2:C2,"<>""")=0,"",YourFormula)
(the last formula checks for formulas returning not an empty string)
etc.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Ralph Howarth" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to have Named dynamic ranges for a worksheet included in
> formulas in another worksheet; but have the formulas automatically add
rows
> of more formulas for each row found in the source worksheet?
>
> I find that I simply have to replicate formulas a good swath of rows to
> ensure that all source data rows are calculated while using many IF
> statements to prevent empty rows of the source data worksheet from
appearing
> as zeros in the formula worksheet.
>
> I have variable rows on the source worksheet where the header row is on
row
> 3 and the formula worksheet has the header row on row 1.

"Arvi Laanemets" wrote:

>...
> But in general, usually there is some column in source workbook
> having non-empty value whenever there are some data in row. Then you can
> easily check it in target workbook. Something like this:
> =IF(SourceSheet!A2="","",YourFormula)
>
The first option is exactly what I am doing with the target worksheet full
of formulas. I simply have a few hundred rows of formulas in the target
worksheet to cover above and beyond the variable number of rows in the data
source worksheet. I suppose this is the best I can do with formulas.

But the formulas are essentially the same for each cell down a given column.
The only difference is the row reference changes down the column. It seems
to me that I could make a macro / VB script that can count the number of rows
in the dynamic range of the data source worksheet and then write on a target
worksheet cell by cell, row by row, the results of formulas applied by VBA.
It seems to me that someone may have already created such automation.

Thanks for your help!

Hi

I have made a couple of workbooks behaving likely, using workbooks Open
event or macro, but there are limits. The problem is the optimal number of
added rows.

I used Open event for cases, the number of added rows is fixed, i.e. every
week a new row is added to table, or for every active object in some objects
list a new row is added every week. The code checks the last weeknumber in
the table, compares it with current weeknumber/month, and adds row(s) for
every missing week/month.

In another application (working time registration), the number of rows
(depends on number of employees in department) with formulas premade is
determined on SetUp sheet, and after changing the number of rows (it must be
at least equal to their number in employees list) the user can start a macro
which redesigns the sheet. The macro counts existing premade rows and adds
or deletes rows depending the number in SetUp table.

When the number of new rows filled by user isn't limited in any way, there
is a considerable risk, that entries are inserted into unprepared rows (when
there is a possibility, the user can do something in wrong way, he does it)

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Ralph Howarth" <[email protected]> wrote in message
news:[email protected]...
> "Arvi Laanemets" wrote:
>
> >...
> > But in general, usually there is some column in source workbook
> > having non-empty value whenever there are some data in row. Then you can
> > easily check it in target workbook. Something like this:
> > =IF(SourceSheet!A2="","",YourFormula)
> >
> The first option is exactly what I am doing with the target worksheet full
> of formulas. I simply have a few hundred rows of formulas in the target
> worksheet to cover above and beyond the variable number of rows in the
data
> source worksheet. I suppose this is the best I can do with formulas.
>
> But the formulas are essentially the same for each cell down a given
column.
> The only difference is the row reference changes down the column. It
seems
> to me that I could make a macro / VB script that can count the number of
rows
> in the dynamic range of the data source worksheet and then write on a
target
> worksheet cell by cell, row by row, the results of formulas applied by
VBA.
> It seems to me that someone may have already created such automation.
>
> Thanks for your help!

The setup table of premade formulas is much what I was thinking as the next
logical step towards automation. I was thinking that a macro can perform a
COUNTA of one column in the source data that represents the key field. From
there have a Setup Worksheet be read with some premade formulas, and then
have a VBA script use a FOR-NEXT loop to calculate a row at a time using the
formulas, then increment 1 for the next row to change the references back to
the source data sheet that one row. Each row lays down until there is
nothing less to copy / calculate over.

I'll be chewing on this one for a while.

Hi

Here is a procedure I used. On sheets for current and previous months for
every row determined in setup are 4 rows in table (4 rows for every
employee)

Public Sub Seadistus()
' Removing passwords
Sheets("JooksevKuu").Unprotect Password:="***"
Sheets("EelmineKuu").Unprotect Password:="***"
Sheets("Nimekiri").Unprotect Password:="***"
Sheets("JK1").Unprotect Password:="***"
Sheets("JK2").Unprotect Password:="***"
Sheets("JK3").Unprotect Password:="***"
Sheets("JK4").Unprotect Password:="***"
Sheets("EK1").Unprotect Password:="***"
Sheets("EK2").Unprotect Password:="***"
Sheets("EK3").Unprotect Password:="***"
Sheets("EK4").Unprotect Password:="***"
' Setting up sheet JooksevKuu (CurrentMonth)
NewNumAll = Sheets("Seaded").Cells(5, 2).Value
LastRow = Sheets("JooksevKuu").Cells.Find("*",
searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 8) / 4
i = 0
Do Until i = NumAllR
If Sheets("JooksevKuu").Cells(9 + 4 * i, 1).Value = "" Then Exit Do
If i > 0 And Sheets("JooksevKuu").Cells(9 + 4 * i, 3).Value = ""
Then
Sheets("JooksevKuu").Range((9 + 4 * i) & ":" & (12 + 4 *
i)).Delete
NumAllR = NmAllR - 1
LastRow = LastRow - 4
Else
i = i + 1
End If
Loop
Select Case NewNumAll
Case Is < NumAllR
Sheets("JooksevKuu").Range((9 + 4 * NewNumAll) & ":" &
LastRow).Delete
Case Is > NumAllR
Sheets("JooksevKuu").Range((LastRow - 3) & ":" & LastRow).Copy
(Sheets("JooksevKuu").Range((LastRow + 1) & ":" & 8 + 4 * NewNumAll))
End Select
'Copying department name from sheet Seaded (SetUp)
Sheets("JooksevKuu").Cells(3, 3).Value = Sheets("Seaded").Cells(1,
2).Value
'Copying department chief name from sheet Seaded
Sheets("JooksevKuu").Cells(4, 3).Value = Sheets("Seaded").Cells(2,
2).Value
' Setting up sheet JK1
LastRow = Sheets("JK1").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("JK1").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("JK1").Range((LastRow) & ":" & LastRow).Copy
(Sheets("JK1").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet JK2
LastRow = Sheets("JK2").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("JK2").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("JK2").Range((LastRow) & ":" & LastRow).Copy
(Sheets("JK2").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet JK3
LastRow = Sheets("JK3").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("JK3").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("JK3").Range((LastRow) & ":" & LastRow).Copy
(Sheets("JK3").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet JK4
LastRow = Sheets("JK4").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("JK4").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("JK4").Range((LastRow) & ":" & LastRow).Copy
(Sheets("JK4").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet EelmineKuu (PreviousMonth)
LastRow = Sheets("EelmineKuu").Cells.Find("*",
searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 8) / 4
Select Case NewNumAll
Case Is < NumAllR
Sheets("EelmineKuu").Range((9 + 4 * NewNumAll) & ":" &
LastRow).Delete
Case Is > NumAllR
Sheets("EelmineKuu").Range((LastRow - 3) & ":" & LastRow).Copy
(Sheets("EelmineKuu").Range((LastRow + 1) & ":" & 8 + 4 * NewNumAll))
End Select
' Setting up sheet EK1
LastRow = Sheets("EK1").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("EK1").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("EK1").Range((LastRow) & ":" & LastRow).Copy
(Sheets("EK1").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet EK2
LastRow = Sheets("EK2").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("EK2").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("EK2").Range((LastRow) & ":" & LastRow).Copy
(Sheets("EK2").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet EK3
LastRow = Sheets("EK3").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("EK3").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("EK3").Range((LastRow) & ":" & LastRow).Copy
(Sheets("EK3").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Setting up sheet EK4
LastRow = Sheets("EK4").Cells.Find("*", searchdirection:=xlPrevious).Row
NumAllR = (LastRow - 2)
Select Case NewNumAll
Case Is < NumAllR
Sheets("EK4").Range((2 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("EK4").Range((LastRow) & ":" & LastRow).Copy
(Sheets("EK4").Range((LastRow + 1) & ":" & 2 + NewNumAll))
End Select
' Seting up sheet Nimekiri (Employees list)
NewNumAll = Sheets("Seaded").Cells(11, 2).Value
LastRow = Sheets("Nimekiri").Cells.Find("*",
searchdirection:=xlPrevious).Row
NumAllR = LastRow - 1
Select Case NewNumAll
Case Is < NumAllR
Sheets("Nimekiri").Range((1 + NewNumAll) & ":" & LastRow).Delete
Case Is > NumAllR
Sheets("Nimekiri").Range((LastRow) & ":" & LastRow).Copy
(Sheets("Nimekiri").Range((LastRow + 1) & ":" & 1 + NewNumAll))
End Select
' Protecting worksheets
Sheets("JooksevKuu").Protect Password:="***"
Sheets("EelmineKuu").Protect Password:="***"
Sheets("Nimekiri").Protect Password:="***"
Sheets("JK1").Protect Password:="***"
Sheets("JK2").Protect Password:="***"
Sheets("JK3").Protect Password:="***"
Sheets("JK4").Protect Password:="***"
Sheets("EK1").Protect Password:="***"
Sheets("EK2").Protect Password:="***"
Sheets("EK3").Protect Password:="***"
Sheets("EK4").Protect Password:="***"
End Sub

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Ralph Howarth" <[email protected]> wrote in message
news:[email protected]...
> The setup table of premade formulas is much what I was thinking as the
next
> logical step towards automation. I was thinking that a macro can perform
a
> COUNTA of one column in the source data that represents the key field.
From
> there have a Setup Worksheet be read with some premade formulas, and then
> have a VBA script use a FOR-NEXT loop to calculate a row at a time using
the
> formulas, then increment 1 for the next row to change the references back
to
> the source data sheet that one row. Each row lays down until there is
> nothing less to copy / calculate over.
>
> I'll be chewing on this one for a while.