Rows added after refresh not in formula

[Guest]

Greetings all. I know I saw the answer to this one recently, but I can not
find it now. I have a workbook with worksheets linked to SQL2K. I have many
formulas like =F6*$G$5. I drag the formula down to one row below the last
row of data on the sheet, and everything works as it should. The problem is
that when I refresh the data if any rows are added they are not included in
the formula, eventhough they are added above where I dragged the formula. Is
there an easy way to make any new rows be included in the formula? Thank
you.

 

[Roger Govier]

Hi Greg

In XL2003 there is a feature called Data>Lists>Create List
Any table set up through this method will automatically have new
formulae created when you append data to the end of the list.
For earlier versions of XL, you have to extend the formulae yourself, or
have a macro to do it.

You could amend the formula to
=F6="","",F6*$G$5
and have it go down the sheet way beyond where your current data is, so
the formula is automatically there as you enter new data at the end of
the table.

You could used Dynamic named ranges to deal with just the used range in
your calculations, rather then the whole range including the "blank
other than formulae" rows at the end of your table.

 

[Guest]

Thanks Roger, that was fast again. The problem is that since the data are
sorted according to the ORDER BY clause in my view in SQL2K new rows are
usually not added at the end, but in the middle. I tried to drag the formula
down to way below where the data will ever be, but still if a new row is
added between the data in row 10 and 11 for example, making what is currently
in row 11 to be row 12 the new row 11 is not contained in the formula. Is
there somewhere I can go to learn about making a macro to do it, because that
is way over my head? And what do the "","" do in the alternative formula you
posted do. I tried entering that formula in a cell but it was giving me an
error. I am using Excel 2003.

 

[Roger Govier]

Hi Greg
Brain fade, that should have read
=IF(F6="","",F6*$G$5)
In other would if null, then use null or use the formula.

Yes, you could have some code to automatically pasted the formula down
the used range(s) after you have imported your data.
I don't have time to write it right now, as dinner is on the table
(perhaps what why the brain was fading <g>), so if you don't have a
response posted by anyone else to provide the code, I will write it
later or tomorrow.

 

[Guest]

Greg -

What about modifying your query to INCLUDE the formula? So...if you are
running a query like

Select
Product,
Price,
Quanitity
from
Inventory
order by
Price

and you want to multiply Price by a discout rate (whatever is in $G$5??) you
could use this query instead - and it would automatically fill in the REUSLTS
for you.

Select
Product,
Price,
Quanitity,
Price*Discount as NetPrice
from
Inventory
order by
Price

 

[Guest]

Roger you are so kind, however, I would not want you to go to the trouble of
doing it for me. I have just in the past few minutes discovered the macro
recorder. I was able to record a macro to drag the formula down to the end
of my data, but the problem is that it only drags to the range recorded. I
need it to be able to know the last row in which there is data. Is this
possible? I do not want the macro to drag the formula down to a row way
below the data for aesthetic reasons. I do not want columns of 0's where
there is otherwise no data. Thank you so much for your help, and enjoy your
dinner.

 

[Guest]

Thanks Duke. I had it that way at first, but my boss asked me to change it.
He wants to be able to manipulate a total dollar amount for each column based
on a value placed in one cell. Since I am the only one here who works on the
server, and I certainly do not want users editing the view even if they had
access or know-how, it is best to do the calculations in Excel.

 

[Guest]

I just thought of something. The value in column A is the primary key for
the table from which the data is pulled, so it will never be null. The
following is what the macro recorder did (what a handy tool).

Range("G6").Select
Selection.AutoFill Destination:=Range("G6:G30"), Type:=xlFillDefault
Range("G6:G30").Select

There are actually identical lines for several columns, but for simplicity's
sake I only listed one. Is there some way to make it do this for all rows in
my selected columns where column A is not null? Also, I saved it as having
to hit Ctrl+F to run it. How can I make it happen after datarefresh?

 

[Guest]

Well, to address your question about copying the formula in a macro, you can
determine the last row of data by doing something like this

dim lrow as long
lrow = Range("c65256").End(xlUp).Row

and then this will populate column A, starting in row 6, with the =F6*$G$5
formula

Range("a6", "a" & lrow).FormulaR1C1 = "= rc6*r5c7"

 

[Guest]

OK, I now see that by showing null my problem of aesthitics goes away.

 

[Guest]

Thanks Duke, but I am confused. Are you saying that something would be
populated in column A? This is not what I need. Column A is a job# that
comes from the SQL view. What I need is for the formulas in certain columns,
G, in my example to exist in all rows in column G where there is a job# in A.
If that is what you are trying to tell me I do not understand. Does any of
this make sense? If not let me know, as I do not know the lingo you all use
to get my point across.

 

[Guest]

these 2 lines of code find the last row of data in column C (just guessing
that this was one of the columns populated by SQL - if not, change the column
letter), assigning the row number to the variable lrow

dim lrow as long
lrow = Range("c65256").End(xlUp).Row

If you change this code (which places your F6*$G$5 formula in every row of
column A from row 6 to the last row of data in column C)

Range("a6", "a" & lrow).FormulaR1C1 = "= rc6*r5c7"

to

Range("G6", "G" & lrow).FormulaR1C1 = "= rc6*r5c7"

It will put the formula in col G.

Is there a possibility of rows coming back from SQL with a null value in A,
and then you don't want the formula to appear in column G? Then use

Range("G6", "G" & lrow).FormulaR1C1 = "= if(rc1="","",rc6*r5c7)"