Defining Excel Template with Named Ranges

[svijay]

Hi,

I have got some problem while creating excel template.

My req is as follows.

I need to create an excel template which contains 2 sections (i.e Named
ranges); with predefined folmula(s).

but the data Im filling in the ranges dynamically thru a web page that
too reading from database.

now Im facing the problem while defining the range in the template.

I just defined Named range 1 as

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),20)

where I fill the data dynamically

Im unable to define range2 in the template bcos I donno the no of rows
that are filled in range1.

So my requirement is I need to define range2 in such a way that it
should read dynamically the last row of range1 and add one row to that
and start filling range 2.

And in range 2 I need to apply a formula (i.e which I suppose to define
in template) to be applied against values of every alternate row of
range2 and to be shown

example

in range 2

first row, first column get value from database.
User will enter a number in second row's first column
based on the number first rows other columns to be calculated
automatically
and thrid row first column get value from database.
user will enter a number in thrid rows first column.
based on the number thrid rows other columns to be calculated
automatically..

like this it shd work.

So I would like to have suggestions from any one of U for the
following

1. Definiging Named rangea in template that reads last row of previous
Named range.
2. Applying formula in alternate rows of Named range2 so that values
calcuated automatically.

thanx in advance to all of U.

Cheers
Vijay

 

[Dave Peterson]

I think you do know how many rows are in named range2. It's close to the number
of rows used in named range1.

For some reason, you offset from A2, but count all of column A's non-empty
cells. (If A1 is empty, then it makes sense to me.)

But I don't think that even naming that range2 will help with applying your
formulas to every other row--especially when the range can grow.

I can think of two alternatives:

Prepopulate the formulas, but check to see if you should try to retrieve the
value:

Kind of like:

=if(a3="","",yourformulahere)

Then if a3 were empty, you won't get anything back.

The second alternative would be to add the formula when something changed in the
worksheet--like if you added a value to A55, then you could put the formula in
B55.

You could use a worksheet_change event kind of like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulaR1C1 As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
If Target.Row Mod 2 = 0 Then Exit Sub

myFormulaR1C1 = "=vlookup(rc1,sheet2!c1:c3,3,false)"

On Error GoTo errHandler:

Application.EnableEvents = False

With Target
If IsEmpty(Target) Then
Me.Cells(.Row, "B").ClearContents
Else
Me.Cells(.Row, "B").FormulaR1C1 = myFormulaR1C1
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

You'll have to put the formula you want into that string.

right click on the worksheet tab that should have the behavior. Select view
code and paste it in.

Back to excel and try it out.

 

[svijay]

Hi Dave,

Nice 2 C Ur reply. But I need to tell you that in in range 2 also not
fixed no of rows and it will depends upon the no of rows in the table.
I need to allow only few columns to the user to change values in every
alternate row in range 2.

But still I did not understand or might not get solution regd
the first problem.

i.e defining the range 2 which takes row position dynamically from the
range 1

Any way thanx once again for your reply

cheers
Vijay

 

[Dave Peterson]

I guess my point was that if the number of rows could vary in range2, then how
would you populate the formulas.

I tried to give alternatives (just do a bunch of them, but have them evaluate to
"" so they look empty) or to add them when the user added more rows.