Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I expand formula down a column when query results change?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I expand formula down a column when query results change?
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 "ssciarrino" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I expand formula down a column when query results chang
Thanks Bernard, I like option #1 ISBLANK.!
"Bernard Liengme" wrote: 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 "ssciarrino" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I expand formula down a column when query results change?
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 "ssciarrino" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I expand formula down a column when query results chang
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 "ssciarrino" wrote: Thanks Bernard, I like option #1 ISBLANK.! "Bernard Liengme" wrote: 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 "ssciarrino" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a delay in formula results when I change one cell amount | Excel Discussion (Misc queries) | |||
converting results from formula to text in a new column | New Users to Excel | |||
How do I change formula results by changing the text color of the | Excel Discussion (Misc queries) | |||
Formula to look up a column and paste results in another column | Excel Worksheet Functions | |||
Formula results in font color change | Excel Worksheet Functions |