Formula that can grow row wise by number of rows of data

[rcbatlinux]

I'm using ODBC to gather data. This data changes in number of rows brought over. I have a formula created that uses this information, but each time I update the data through ODBC the number of rows returned changes. So either my copied formula has too many rows and leaves bogus information or the formula isn't copied down enough rows to match the data coming in.

Any way to have the formula match each row of data returned? I'm leaning towards a Macro, but wanted to know if there are any other tricks.

Rick

 

[JVFriederick]

1) Use a filter to hide records which have formulas but no data. Add a counter on spreadsheet to display "Error" if ODBC records returned are greater than rows of formulas.

You can turn of the filter manually or use macro code something like :

Range("FilterStart&quot.Select
Selection.AutoFilter ' Turn filter on with criteria
Selection.AutoFilter Field:=1, Criteria1:="1"


2) Use a macro to create a formula in desired column after counting the number of records returned.

I don't have this handy, but would could look it up tonight if that's fast enough.

 

[mala]

Suppose your data is contained in cols. A thru F and the formula in col G. I presume the fistrow contains field headers. Let this be in a sheet called "MySht"
Insert a module in the VBA project of the Workbook and enter the following module
Let the name of the formula be 'MyFormula' - (u can define a name for a formula as you do for ranges)

Sub Update()
Dim LastCell as range
Sheets("MySht&quot.activate
Set LastCell= Range("A65535&quot.end(xlup)
Range("G2:G65535&quot.clearcontents
Range("G2",LastCell.Address).Formula = "=MyFormula"
Instead of this you can also specify the formula in RC form