Dynamic Range in Excel that won't count formulas
I have an excel file that I constantly have to update the the ranges
for some of my formulas because sometimes the data fills A1:A35 or sometimes can go down to A1:45. There are formulas that pull data from other sheets in cells A1:A45 (if there is data). I would like the range to only include those cell values that actually have values and not the remaining formulas that return blanks. How do I do this? Thanks in advance for your help. Conor |
Dynamic Range in Excel that won't count formulas
Assuming that the blank cells are all at the bottom of the range:
=OFFSET(A1,,,SUMPRODUCT(--(LEN(A1:A100)0))) -- Biff Microsoft Excel MVP wrote in message ... I have an excel file that I constantly have to update the the ranges for some of my formulas because sometimes the data fills A1:A35 or sometimes can go down to A1:45. There are formulas that pull data from other sheets in cells A1:A45 (if there is data). I would like the range to only include those cell values that actually have values and not the remaining formulas that return blanks. How do I do this? Thanks in advance for your help. Conor |
All times are GMT +1. The time now is 09:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com