largeselection
Active Member
- Joined
- Aug 4, 2008
- Messages
- 358
I love this forum. I used to post here all the time and I can honestly say that the help I have gotten from people here has helped me become an OK Excel user. So I have another question and I'm wondering what you all think might be the best way to accomplish this.
I have a workbook which users enter quantities into. The enterable sections on the worksheet are not contiguous unfortunately. Ultimately I want to pull together one database on a different worksheet which contains the quantity the user entered, the value in columns A, B, and F for the same row as the quantity entered and the value in row 3 for the same column as the quantity entered.
So I might have quantities entered in H17:M50, and some other quantities in Q17:W50, and some others in H60:M99, etc.
I have created some code which accomplishes this. Basically I have created named ranges for each of the enterable sections, the column before each enterable section contains a value which is equal to the value in the same row and columns A, B, and F concatenated. I then create a multiple consolidation range pivot table and then "show details" and it spits out a nice table of data which has the concatenated value of column A, B, and F, as well as the column header. I then use some formulas to extract out the values of column A, B, and F into their own columns. Then I add like 50 columns of other formulas and lookups.
This all works OK, the only downside is it can take up to 45 seconds to refresh the table and also requires someone to actually click a button to pull and refresh the data.
So I wanted to try and make it more dynamic so I changed it to more of a formulaic approach which would basically do lookups and pull all the possible datapoints from those same named ranges I spoke about. The downside to that was that in order to keep it dynamic I had to account for every possible combination and datapoint (even though many may not have been used). And this came to 100,000 rows. The lookups and everything work fine, but anytime I make a change in those enterable sections it takes about 1-2 seconds to refresh since it is calculating across 100,000 rows.
The benefit to this method, however, is that I can create some running recaps based on that database worksheet and no one has to click "refresh" in order to update the database first.
So I guess my question is - are there any ways to speed up calculation? I've separated my index/match lookups into separate index and match columns since many reference the same row and that helped, but not as much as I'd like. I can't really sort the worksheet with the enterable cells to be sorted ascending to speed up the lookup.
Any other ideas on how I could get this done? Basically I want people to be able to enter their quantities in the enterable sections and have some formulas update on that worksheet and I want the quantities and those three values from A, B and F and the associated value in row 3 of the same column populate into a database actively while they enter quantities so that I can run reporting off the database rather than having to rely on them refreshing the database as they do now and take a minute to do that.
I considered maybe doing a worksheet_change routine where it would transfer the column values, and row 3 value using offset, but I figured that would probably really slow down the worksheet considerably considering There are about 2000 rows x 50 columns of enterable cells.
Is there a way to speed up some lookup formulas to only execute if there is a value to lookup? At the moment it takes time to calculate even if it is trying to do a lookup of a blank value? If I put an if statement that says if it's blank then leave it blank otherwise do the lookup does that help with calculation speed?
Sorry for the ENORMOUS post, but I wanted to be clear.
Please ask any question you may have and I"ll do my best to answer. Thank you in advance for any help and even for just taking the time to read this!
I have a workbook which users enter quantities into. The enterable sections on the worksheet are not contiguous unfortunately. Ultimately I want to pull together one database on a different worksheet which contains the quantity the user entered, the value in columns A, B, and F for the same row as the quantity entered and the value in row 3 for the same column as the quantity entered.
So I might have quantities entered in H17:M50, and some other quantities in Q17:W50, and some others in H60:M99, etc.
I have created some code which accomplishes this. Basically I have created named ranges for each of the enterable sections, the column before each enterable section contains a value which is equal to the value in the same row and columns A, B, and F concatenated. I then create a multiple consolidation range pivot table and then "show details" and it spits out a nice table of data which has the concatenated value of column A, B, and F, as well as the column header. I then use some formulas to extract out the values of column A, B, and F into their own columns. Then I add like 50 columns of other formulas and lookups.
This all works OK, the only downside is it can take up to 45 seconds to refresh the table and also requires someone to actually click a button to pull and refresh the data.
So I wanted to try and make it more dynamic so I changed it to more of a formulaic approach which would basically do lookups and pull all the possible datapoints from those same named ranges I spoke about. The downside to that was that in order to keep it dynamic I had to account for every possible combination and datapoint (even though many may not have been used). And this came to 100,000 rows. The lookups and everything work fine, but anytime I make a change in those enterable sections it takes about 1-2 seconds to refresh since it is calculating across 100,000 rows.
The benefit to this method, however, is that I can create some running recaps based on that database worksheet and no one has to click "refresh" in order to update the database first.
So I guess my question is - are there any ways to speed up calculation? I've separated my index/match lookups into separate index and match columns since many reference the same row and that helped, but not as much as I'd like. I can't really sort the worksheet with the enterable cells to be sorted ascending to speed up the lookup.
Any other ideas on how I could get this done? Basically I want people to be able to enter their quantities in the enterable sections and have some formulas update on that worksheet and I want the quantities and those three values from A, B and F and the associated value in row 3 of the same column populate into a database actively while they enter quantities so that I can run reporting off the database rather than having to rely on them refreshing the database as they do now and take a minute to do that.
I considered maybe doing a worksheet_change routine where it would transfer the column values, and row 3 value using offset, but I figured that would probably really slow down the worksheet considerably considering There are about 2000 rows x 50 columns of enterable cells.
Is there a way to speed up some lookup formulas to only execute if there is a value to lookup? At the moment it takes time to calculate even if it is trying to do a lookup of a blank value? If I put an if statement that says if it's blank then leave it blank otherwise do the lookup does that help with calculation speed?
Sorry for the ENORMOUS post, but I wanted to be clear.
Please ask any question you may have and I"ll do my best to answer. Thank you in advance for any help and even for just taking the time to read this!
