Adjusting a formula cell range

J

Jamie

I have a formula that uses cells D2:D20, the problem is the info that it is
using is updated through a query, so I might need the range to be D2:D30, if
I set the range to larger than what i need it causes an error in the formula,
is there a way to make excel recognize whether there is a value in the cell
or not and include it in the formula range so the formula range floats with
the data that arrives through the query.

Thanks
 
D

Daniel CHEN

Use the following to create a dynamic range:

OFFSET(D2,0,0,COUNT(D2:D99),1)

Replace D2:D20 in your formula with the above dynamic range.
99 can be changed to any big number.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use TODAY() function as the criteria in a SUMIF formula 1
Looking to added up based on a character from cell range 1
need help in fine-tuning a formula that removes blank cells in a column range 3
Average problem 1
copy formula 2
Auto-adjusting Formulas 1
Macro adds "=" to each cell in defined range across multiple worksheets 5
Formula Assistance 8

Top