G
GoBobbyGo
I have a set of forms that I maintain that each of our offices needs to use,
which are linked together with MSQuery.
We originally created the queries with the default "insert cells for new
data, delete unused cells" property selected, and we heavily rely on the
"fill down formulas in columns adjacent to data" feature.
This led to problems, though - another page linked to the data query page,
where cell A1 linked to the first line of the query, A2 linked to the second
line of the query, etc. When the query turned up more rows than previous, A1
linking to the first line of the query, A2 linking to, say, the fifth line,
etc, and nothing linking to the newly-created lines in the query. When they
query turned up fewer lines than previous, it led to #REF! errors in A2, A3,
etc, as the cells they were linking to had disappeared.
We got around this by building in a macro that would re-establish the
formlas in those cells - so every time we refreshed the query, we had to fix
all the formulas on the other page. Pretty clunky.
Now we're designing next year's versions of these forms, and one of the
changes we made was to use the "overwrite existing cells with new data, clear
unused cells" - testing the forms with fake data, this seemed to work like a
charm. But cutting the forms down to being ready for the offices meant
starting afresh with no data. Which led to this revolting development:
When the query is refreshed and returns no results (as will happen with the
blank forms), all the formulas in the adjacent columns are completely,
utterly lost.
Is there a way to fix this so that, even if the query returns a null result,
I don't lose the adjacent formulas?
which are linked together with MSQuery.
We originally created the queries with the default "insert cells for new
data, delete unused cells" property selected, and we heavily rely on the
"fill down formulas in columns adjacent to data" feature.
This led to problems, though - another page linked to the data query page,
where cell A1 linked to the first line of the query, A2 linked to the second
line of the query, etc. When the query turned up more rows than previous, A1
linking to the first line of the query, A2 linking to, say, the fifth line,
etc, and nothing linking to the newly-created lines in the query. When they
query turned up fewer lines than previous, it led to #REF! errors in A2, A3,
etc, as the cells they were linking to had disappeared.
We got around this by building in a macro that would re-establish the
formlas in those cells - so every time we refreshed the query, we had to fix
all the formulas on the other page. Pretty clunky.
Now we're designing next year's versions of these forms, and one of the
changes we made was to use the "overwrite existing cells with new data, clear
unused cells" - testing the forms with fake data, this seemed to work like a
charm. But cutting the forms down to being ready for the offices meant
starting afresh with no data. Which led to this revolting development:
When the query is refreshed and returns no results (as will happen with the
blank forms), all the formulas in the adjacent columns are completely,
utterly lost.
Is there a way to fix this so that, even if the query returns a null result,
I don't lose the adjacent formulas?