I'm working working on a report were I query an Access database. Obviously the database is not static so the number of results that come back changes as data is entered into the Access database. How do I setup formula's so that they retract or expanded based on the number of results the query brings back to Excel.
I hope the above question makes sense,
Thanks,
Kevin
Good evening kdarr
...and welcome to the forum!
Perfect sense. What you ned to use would probably be dynamic range names, which is a named range which sets itself to the end of your data - you then use this in your formula rather than the actual range. Once set up this range will adjust as data is added / removed.I hope the above question makes sense,
Look here for help on dynamic ranges :
http://www.cpearson.com/excel/named.htm
My add-in (see below) also has a dynamic named range creator.
If you need further help, you'll need to post some of your formulae that you want to work on.
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
It would probably be easier if I could post a section of the workbook, but for confidentality purposes I can't. Here's one of the formula's I use. How would I make it "dynamic"?
=IF(AND(D2>=38899,D2<=38990),1,IF(AND(D2>=38991,D2<=39082),2, IF(AND(D2>=39083,D2<=39172),3, IF(AND(D2>=39173,D2<=39263),4,0))))
Thanks for all your help...
Kevin
Hi kdarr
If this is typical of the formulae you've got them dynamic formulae might not be the way to go. I assumed you would be doing things to whole columns at a time.
What is in D2? Why might D2 be in a different position next time the query is run? How far is it likely to move and in what direction?
DominicB
D2 is a date, and I use the formula to determine which quarter of the year the date falls within....I've copied this formula manually all the way down the column as far as my data goes, but when the data is refreshed there are usually more entries in the database. So, I'm trying to get my formula's to expand and retract based on the number of entries returned from my query.
This is just an idea that may or may not work for you. Why not copy the formulas down as far as you believe the database may go. For example, if you think the maximum column D would go is 100 rows, try this nestinging 1 more IF function, and fill it down to row 100. It would look like this:
=IF(D2="","",IF(AND(D2>=38899,D2<=38990),1,IF(AND(D2>=38991,D2 <=39082),2, IF(AND(D2>=39083,D2<=39172),3, IF(AND(D2>=39173,D2<=39263),4,0)))))
Now the formula checks if D2 is blank. If so, it returns a blank. Otherwise, it calculates. This way, you can fill the formula BEYOND your data, and the cells beyond the data will remain blank.
Let me know if that may work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks