Arcady
New Member
- Joined
- Oct 30, 2009
- Messages
- 42
This is probably more information than is strictly necessary, but my project in question is a model of a game. The idea is to calculate the current state at any given time. The workbook contains pages labeled P01 through P99. I have a specific column of data (column DV) that I need to pull from all 99 sheets and compile into a list on one page while removing the blank spaces. I think I've figured out how to accomplish the task by calling each cell individually with this formula:
=IF(ISERROR(INDIRECT("P01!A1")), "", IF('P01'!$DV64="", "", 'P01'!$DV64))
I'm using INDIRECT because the actual number of pages in the workbook is user defined. I would then use a VLOOKUP formula to compress the list and remove the blanks.
The first problem here is that each page can calculate up to 1000 turns (more really, but 1000 should be plenty) So that's 1000 turns of data stretched across 99 pages, so about 100,000 formulas just to rebuild the list. Add another 100k for the VLOOKUP addressing and this gets very large indeed. Now, in all actuality, the number of items in the final list most likely won't exceed 10,000. My goal is to find a formula that I can use 10,000 times and index all the pages properly while removing the blanks, but I'm not sure what I can do about that.
The second problem with the above idea is that I would almost have to manually edit all of the formulas to increment 'P01' through the rest of the worksheets.
And then of course there is the 64k row limit, so I would have to break it up a bit, though I don't think that would be a true problem.
Well, here's a link to the project. http://www.mediafire.com/?sharekey=129af724f086724690a82c7bb0fad7ade04e75f6e8ebb871
Just keep in mind that it's still under construction.
=IF(ISERROR(INDIRECT("P01!A1")), "", IF('P01'!$DV64="", "", 'P01'!$DV64))
I'm using INDIRECT because the actual number of pages in the workbook is user defined. I would then use a VLOOKUP formula to compress the list and remove the blanks.
The first problem here is that each page can calculate up to 1000 turns (more really, but 1000 should be plenty) So that's 1000 turns of data stretched across 99 pages, so about 100,000 formulas just to rebuild the list. Add another 100k for the VLOOKUP addressing and this gets very large indeed. Now, in all actuality, the number of items in the final list most likely won't exceed 10,000. My goal is to find a formula that I can use 10,000 times and index all the pages properly while removing the blanks, but I'm not sure what I can do about that.
The second problem with the above idea is that I would almost have to manually edit all of the formulas to increment 'P01' through the rest of the worksheets.
And then of course there is the 64k row limit, so I would have to break it up a bit, though I don't think that would be a true problem.
Well, here's a link to the project. http://www.mediafire.com/?sharekey=129af724f086724690a82c7bb0fad7ade04e75f6e8ebb871
Just keep in mind that it's still under construction.
