formulas and external data

[seth]

i have an issue trying to calculate data based on a query
i import the data with all my columns and records, at the end i insert a
column with a formula that calculates 4 different cells in the row
then i run a query to remove null (zero) value data from a certain field,
then the formual column that i added manually doesn't look right.

for example, if i have 800 rows of data, i copied that formula to all 800
rows in the last column following the external data
when i run the query, all the zero values are removed (doing what it should)
and the formulas stay, except the last row that is labeled #REF instead of
the formula, and all other values below it have that same #REF in it.

any ideas as to how to get around that? to only maybe have the formulas
appear on the cells that are used. i tried using the if statement, checking
to see if a value in that row was greater than zero, then calculate if
positive result. that was no different than before. still have the #REF
from the last row of data all the way down to the 800th row

 

[Arvi Laanemets]

Hi

What do you mean with 'i run a query to remove null (zero) value data' ? It
hardly is an ODBC query, is it? Do you run a macro?

Based on your message, it looks like you deleted some cells you formulas
were referring to, or you shifted remaining data after deleting. As result
row references are messed up. When you have formulas referring to cells on
sheet, the only way to delete something safely is to delete the entire row
or column (and this too not always). But better use Clear Contents instead.


Arvi Laanemets

 

[seth]

in the query, i select the field, and for the value i put <>0
from 800+ it only shows maybe 15 and that works fine
it's the part about using a formula with it
if i put in a formula at the end column for all 800+ rows then run the
query, i get #ref

 

[Arvi Laanemets]

Hi

Can't you explain it more clearly? In Excel, when you speak about queries,
then usually it means you did select from Data menu 'Get External Data' etc
(there are other ways too, p.e. you can invoke a query through VBA - but one
way or another, always is defined some SQL string and then it's processed).
The query gets data from some (external) source (Excel or Access or FOxPro
or whatever table), and inserts retrieved data into destination table. Is
that you are speaking about?

And when not? When you have some VBA procedure, then maybe post it here.

 

[seth]

yes of course
doing a query can only be done after getting external data; thought that
would have been obvious (sorry)
but after the data is imported, it's getting a formula to work based on that
query
i'm not too good with vba and sql statements

 

[Arvi Laanemets]

Hi

After you created a query, you can set properties for it (select a cell from
returned datarange, and from Data menu select
GetExternalData.DataRangeProperties). There is an option to set formulas in
adjacent columns to be adjusted. Check it, enter your formulas into adjacent
columns, copy them to all rows there are returned query data present, and
you are done with it. When query is refreshed, formulas are deleted or added
accordingly the number of returned rows.


Arvi Laanemets

 

[seth]

hey it worked
thanks

Hi

After you created a query, you can set properties for it (select a cell
from
returned datarange, and from Data menu select
GetExternalData.DataRangeProperties). There is an option to set formulas
in
adjacent columns to be adjusted. Check it, enter your formulas into
adjacent
columns, copy them to all rows there are returned query data present, and
you are done with it. When query is refreshed, formulas are deleted or
added
accordingly the number of returned rows.


Arvi Laanemets