Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ralph Howarth
 
Posts: n/a
Default 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.
  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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=""),"",Yo urFormula)
or
=IF(COUNTIF(SourceSheet!A2:C2,"<")=0,"",YourFormu la)
or
=IF(COUNTIF(SourceSheet!A2:C2,"<""")=0,"",YourFor mula)
(the last formula checks for formulas returning not an empty string)
etc.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Ralph Howarth" wrote in message
...
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.



  #3   Report Post  
Ralph Howarth
 
Posts: n/a
Default

"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!
  #4   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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<attarkon.ee
Arvi Laanemets


"Ralph Howarth" wrote in message
...
"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!



  #5   Report Post  
Ralph Howarth
 
Posts: n/a
Default

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.


  #6   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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<attarkon.ee
Arvi Laanemets


"Ralph Howarth" wrote in message
...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make Excel run limited number of formulas on a given worksh John Excel Discussion (Misc queries) 0 January 12th 05 05:29 PM
Way to make Excel only run certain formulas on a worksheet? jrusso Excel Discussion (Misc queries) 0 January 12th 05 05:23 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 12:29 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM
calculating formulas for all workbooks in a folder Chad Excel Worksheet Functions 3 November 13th 04 06:22 PM


All times are GMT +1. The time now is 06:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"