Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-adjust formulas
Is there a way to set up your formula in such a way that when your source
data column is longer or shorter that the formula will automatically detect that and adjust for it? example: Column B is 38 lines long this time but when I ran it yesterday it was 35 lines long so the last 3 lines were not captured in my data today and i am using 2003 and my formulas do not allow for selecting the entire column thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-adjust formulas
Without an example of what your formula looks like, it makes it harder for us
to suggest what you can do. If you can't simply make the range excessively large, you could use dynamically named ranges. An example: Go to Insert - Name.. - Define Use any name you want (MyRange) Refers to: =OFFSET(B1,0,0,COUNTA(B:B),1) Hit "Add", and then "ok" Your formula can now reference the named range =SUM(MyRange) This formula counts how many non-blank cells are in column B (say 38) and thus the entire formula returns the range B1:B38. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "se7098" wrote: Is there a way to set up your formula in such a way that when your source data column is longer or shorter that the formula will automatically detect that and adjust for it? example: Column B is 38 lines long this time but when I ran it yesterday it was 35 lines long so the last 3 lines were not captured in my data today and i am using 2003 and my formulas do not allow for selecting the entire column thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-adjust formulas
That works! Thanks!
"Luke M" wrote: Without an example of what your formula looks like, it makes it harder for us to suggest what you can do. If you can't simply make the range excessively large, you could use dynamically named ranges. An example: Go to Insert - Name.. - Define Use any name you want (MyRange) Refers to: =OFFSET(B1,0,0,COUNTA(B:B),1) Hit "Add", and then "ok" Your formula can now reference the named range =SUM(MyRange) This formula counts how many non-blank cells are in column B (say 38) and thus the entire formula returns the range B1:B38. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "se7098" wrote: Is there a way to set up your formula in such a way that when your source data column is longer or shorter that the formula will automatically detect that and adjust for it? example: Column B is 38 lines long this time but when I ran it yesterday it was 35 lines long so the last 3 lines were not captured in my data today and i am using 2003 and my formulas do not allow for selecting the entire column thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto-adjust formulas
Hi,
Highlight B1:B38 and convert it to a List (Data List). Once you do so, the range becomes auto expanding. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "se7098" wrote in message ... Is there a way to set up your formula in such a way that when your source data column is longer or shorter that the formula will automatically detect that and adjust for it? example: Column B is 38 lines long this time but when I ran it yesterday it was 35 lines long so the last 3 lines were not captured in my data today and i am using 2003 and my formulas do not allow for selecting the entire column thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell width auto adjust | Excel Discussion (Misc queries) | |||
how do i get columns in Excell to auto-adjust | Excel Worksheet Functions | |||
Auto adjust dates in a chart | Charts and Charting in Excel | |||
Auto Adjust Scale | Charts and Charting in Excel | |||
auto adjust columns in a pivot | Excel Worksheet Functions |