How do I expand formula down a column when query results change?

S

ssciarrino

I have a spreadsheet that populates Column A through H via Microsoft Query.

Columns I through J are formulas using Columns A-H

Each month the query is refreshed, when refreshed the rows of A-H can expand
or contract

If the current month is 100 rows and refreshes to 105 rows, the formulas
from I-J do not copy down (nor do I expect them to)

Looking for a way for when Columns A-H change, the related columns I-J also
change.
 
B

Bernard Liengme

Copy the formulas down as far as you ever expect data (and add 100!)
But the formulas will give odd results you say.
Try one of these
=IF(ISBLANK(A1),"",your-formula)
=IF(COUNT(A1:H1)=8, your-formula, "")

best wishes
 
S

ssciarrino

Thanks Bernard, I like option #1 ISBLANK.!

Bernard Liengme said:
Copy the formulas down as far as you ever expect data (and add 100!)
But the formulas will give odd results you say.
Try one of these
=IF(ISBLANK(A1),"",your-formula)
=IF(COUNT(A1:H1)=8, your-formula, "")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
Click to expand...
 
F

FSt1

hi
right click the MSQ(MicroSoft Query) data range(anywhere).
On the popup, click Data Range Properties.
from the dialog(at the bottom), check "fill down formulas in columns
adjacent to data"

all of your formula will expand and/or contract with the data at each
refresh. you can also have formulas at the bottom and they too will adjust
with the data refresh. you CANNOT put formulas inside the MSQ data range. the
MSQ data range is like a named range that expands and/or cotracts with each
refresh and all cells inside the MSQ data range is reserved for the MSQ.

regards
FSt1
 
J

James Gough

Hi Guys

How would I do this in Microsoft Office 2007? It appears that the option to
right click and fill down formulas has now dissappeared.

Any ideas

Regards

James
 
Top