External data sources and file sizes

[rndContractor43]

I have a worksheet that I paste a bunch of data into everyday (~3mb), it already has some look up tables in it that get manually updated, I do some analysis on it, and then email it out to my team. It's ~5mb-6mb when all is said and done. It's part database, part analysis, part report. Not my design (it's inherited) and while I can change what I want, it seems to work fine for our purposes.

I have some new data I would like to add, but it winds up adding ~2mb to the file size, which I'd rather not do. I've already optimized as much as I can by flushing pivot caches, clearing formatting, and the usual recommendations.

I'd like to break out the data sources, but Excel gets kinda weird about referring to other spreadsheets, some functions require the spreadsheet to be open, and the references randomly change/die. I like to datestamp filenames too, so that makes it a royal pain.

I recently learned that you can have external CSV files to reference, and I have some questions about that:

1. Does Excel copy the data into the spreadsheet, therefore creating a larger file anyways?
2. What happens when I email the file and the receivers don't have the CSV files? I have formulas, VB code, and Pivot Tables. Presumably the formulas will fail, but what about Pivot Tables?

What other considerations or options should I consider?

Yes, this thing should be a database, but I'm still expanding it's functionality, while I also need to use it all the time and send out copies of it, and I'm not familiar enough with Access to add that dev work to the pile.

No, you can't have a copy, nor can I make a sample of it to share.

[CRIMEDOG]

How are you pasting in? Are you pasting in formats also? And are you selecting the whole page or just the data.
Cells that look empty aren't always empty.
Select the cell to the right of your data, [ctrl][shift][right arrow] then delete columns
Select the cell below your data [ctrl][shift][down arrow] then delete rows
Do this on all your tabs.
Only select the data you need and paste values (to avoid formatting) - this could reduce your file size

Consider limiting the time frame of the data - you could have a sheet that summarizes previous data (without all the input)

Start playing around with Access - It could help you out. There are short youtube tutorials out there

[rndContractor43]

1. How are you pasting in? Are you pasting in formats also? And are you selecting the whole page or just the data.
2. Cells that look empty aren't always empty.
3. Select the cell to the right of your data, [ctrl][shift][right arrow] then delete columns
4. Select the cell below your data [ctrl][shift][down arrow] then delete rows
5. Do this on all your tabs.
6. Only select the data you need and paste values (to avoid formatting) - this could reduce your file size

7. Consider limiting the time frame of the data - you could have a sheet that summarizes previous data (without all the input)

8. Start playing around with Access - It could help you out. There are short youtube tutorials out there

1. It's from a CSV file, there is no formatting. I'm only copying the data.
2. Yes, I know.
3. See #2.
4. See #2.
5. See #2.
6. I do. I already have the minimum amount of data.
7. See #6.
8. I looked at it already, but that's a project in and of itself and I'm busy with other things.


I've gotten good info from this forum before as a lurker, but did you even bother to read my post and see which parts of it I needed help with? It's like you stopped reading as soon as you saw "it's a big file".

[FDibbins]

1. It's from a CSV file, there is no formatting. I'm only copying the data.
2. Yes, I know.
3. See #2.
4. See #2.
5. See #2.
6. I do. I already have the minimum amount of data.
7. See #6.
8. I looked at it already, but that's a project in and of itself and I'm busy with other things.


I've gotten good info from this forum before as a lurker, but did you even bother to read my post and see which parts of it I needed help with? It's like you stopped reading as soon as you saw "it's a big file".

wow, perhaps you overlooked the part where common courtesy is the order of the day? I would suggest, that if intend to keep getting assistance from us, you take a more polite attitude. Our members are here to help, and most will just walk away from a post with comments like you have made.

If my comment causes you to add a few dozen more emoticons, dont bother, just leave.

Admin

[rndContractor43]

So, Ford, how would you respond to a response that is disrespectful?

The emoticons are because tone and inflection are lost through the internet, which is a well known and longstanding phenomenon of the internet.

[FDibbins]

I fully understand the use of emoticons (been using computers for 40+ years now), and, just like cursing, are very often so over-used they lose impact. Less is often more.

I see no disrespect, just some valid questions. Remember, if a member - trying to help you - does not understand what you want or thinks they need extra info, they ask questions...often to make sure the obvious is covered. We also have no way of knowing skill/knowledge level of brand new members - like yourself - and tend to sometimes err on the side of caution to make sure an understanding is reached.

On the same foot, how often have you typed something that multiple people have read, and had multiple different interpretations of your text? that happens on here so often that we all come to expect ....3 people read the same thing, and respond in 3 different directions.

If you feel a question was asked that you had already covered, a simple redirect to where you believe you answered is all that is needed. Please keep in mind that this is a FREE service, offered by people from all walks of life, giving their time and expertise as and when they can. If you alienate them, you are the 1 that suffers, not them, I give the same instructions to everyone - if you dont like the response you get from an OP, or just dont like an OP, let it go and move on to the next person, who will more than likely be far more appreciative of your efforts.

You may have noticed the lack of response since your post #3? I suspect many have just moved on with no desire to work with someone some may consider as rude.

Hope that helps clear things up a bit?