dynamic_ranges_and_sheets_question.xlsx
Hello all
I´m having problems with the max formula using dynamic ranges in different sheets.
I need to use ranges involving the last rows of different sheets, but the problem is that each of them are updated daily (new rows are added).
Please check the attached file for a more detailed explanation of the problem.
I´ll highly appreciate any help you can provide.
Thanks in advance.
Last edited by nicoan; 02-24-2014 at 02:07 PM.
Convert each of the Symbol sheets to Tables
Rename the Table as "tab" & Symbol
For example: tabLYG, tabMTU, etc
Formula:
Please Login or Register to view this content.
See the attached updated example.
Regards, TMS
Last edited by TMS; 02-23-2014 at 08:49 PM.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMS, and thanks so much for the response.
The problem is that I´m using a software to download and update the historic data, and currently using 400+ symbols/sheets.
Is not efficient to add the tables manually, and the individual sheets must remain untouched for the software to download the updates.
Any work around this? Like without using tables in the sheets?
Thanks a mil.
The reason for suggesting Tables is that they automatically maintain Named Ranges for the columns. I guess you could create Dynamic Named Ranges for each sheet, but I think you'd need two for each sheet. One for the date column and another for the Close Price. A lot of Dynamic Named Ranges.
I don't think the software would/should treat a sheet with a Table any differently. And you could easily convert each sheet to a Table using VBA.
Regards, TMS
I´ve tried the updates and the software does remove the tables on every update.
Apart of that your formulas work like a Swiss clock.
Since the updates run once a day, I could still use the formulas as you said running a macro after them that converts all symbol sheets to tables and name them as needed.
I hate to ask too much but I´m completely in the dark with VBAs. Can you provide any help with it?
Ahhh, ... Money and mouth, huh?
Give me a while, I'll have a look at it.
Regards, TMS
OK, this is the code to add the Tables and Formulae:
Please Login or Register to view this content.
Please see the attached updated workbook to try it. I don't know how it will perform with 400 sheets ...
Regards, TMS
I note that you have been logged on the forum since I provided a solution for you.
Does it meet your needs?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
**Perfect**
Man, you rock.
My karma points to you. Best wishes.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks