Populate 65k cells from Access

[thepanu]

Any ideas on what would be the best option to populate 65k cells in excel from access?

So far I have tried to options.

First, import all relevant rows to separate sheet in same file and access these with vlookup. Works, but i get the feeling that not as elegant solution as it could be.

Second, create custom formula that queries database for individual values. opens and closes db connection each time. SLOW.

To further clarify my situation. I have weeks of year in columns and products on rows. Each product has 7 rows, one for each unit. So I fetch the value based on product id, week and unit id. Values are unique and I have identifier field in database for this purpose. Cell values are quantities.

So for example product 001 on week 1 for unit AAA identifier is 001#1#AAA.

Also, there are rows for manual dataentry and sums for every product. These stay in this file and don't have any connection to access. All units might have all products but might as well not.

Any ideas?

 

[xenou]

Also, there are rows for manual dataentry and sums for every product. These stay in this file and don't have any connection to access. All units might have all products but might as well not.

65,000 rows of this kind of thing just sounds like a bad idea from start to finish.

 

[thepanu]

65,000 rows of this kind of thing just sounds like a bad idea from start to finish.

65k Cells, not rows. 180 products, 7 units and 52 weeks.

 

[xenou]

It's still too much for a spreadsheet. Plus I would recommend against mixing manual data entry with formulas and query tables. Keep the data and data entry separate from the reporting - this spreadsheet is much too busy and (probably) very difficult to use and maintain.

 

[thepanu]

Problem is that manual entry row is edited based on data on 7 previous rows. They can't be on different pages, would be impossible to get any work done.

In my current implementation I copy data from units and show only sum, so 7 times less rows. But there is need to show unit level data and not only sum.

So it would seem that my best bet seems to be to import whole dataset to separate page and use vlookup to fetch values. Performance seems to be okay.

 

[xenou]

Okay - that sounds reasonable enough. I've used such a setup many times.
ξ