or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 27

post #391 of 533
Quote:
Originally Posted by Neo1824 View Post
this one has me stumped. I have a Excel 2003 file with numberous vlookups in it that look to another excel file to find the data. so...

file1 has vlookup and points to file2 to find the data.

so for some reason file1 is caching the data it looks up such that even when the linked file is unavailable, lookup data shows up. This is causing the file to get quite large.

Does anyone know how to turn off this feature or prevent it from happening?

This should not be happening. Are you sure that it is not something else? A pivot table perhaps?
post #392 of 533
Quote:
Originally Posted by Neo1824 View Post
this one has me stumped. I have a file with numberous vlookups in it that look to another excel file to find the data. so...

file1 has vlookup and points to file2 to find the data.

so for some reason file1 is caching the data it looks up such that even when the linked file is unavailable, lookup data shows up. This is causing the file to get quite large.

Does anyone know how to turn off this feature or prevent it from happening?

Do you want the current set of data and no more changes or updates to it? Worst case scenario you just do past special values and remove all formulas.
post #393 of 533
Quote:
Originally Posted by Bhowie View Post
Do you want the current set of data and no more changes or updates to it? Worst case scenario you just do past special values and remove all formulas.

Definitely not a pivot table. It's all vlookups.

I have to keep the formula's in there so that the data refreshes each time we open the file.

It's quite strange. The source data file is located on a server and I sometimes get data popping in even when I'm not connected to my work network at all.
post #394 of 533
Quote:
Originally Posted by Neo1824 View Post
Definitely not a pivot table. It's all vlookups.


What I meant to ask is whether you have pivot tables in other parts of your workbook. This could be creating the large size.
post #395 of 533
nope, no pivot tables in the workbook.
post #396 of 533
Quote:
Originally Posted by Neo1824 View Post
nope, no pivot tables in the workbook.

vlookups which reference other spreadsheets can get huge easily. are you talking about a few thousand rows of vlookups? One way is to go to options and turn to manual calc. So it doesn't refresh and take up heaps of time if you are amending the xls. Press Shift+F9 to recalculate single sheet, or F9 to recalculate workbook.
post #397 of 533
Quick question regarding pivot tables--should be pretty easy but I can't figure it out.

Let's say I have a pivot table with rows of brands --> items within brand --> year and column of months

Formatted so that I can easily see YoY Lifts, etc per item. However, I would also like to see YoY Lifts per brand, yet because the year is a row as well, it rolls up all years per month on the brand subtotal. Therefore, for Brand A in Oct. I am getting Oct 2001-2010, as opposed to just a subtotal of Brand A for Oct 2010. I would like the brand to subtotal per month per year, just like the skus do.

Help?
post #398 of 533
Thread Starter 
Quote:
Originally Posted by ginlimetonic View Post
vlookups which reference other spreadsheets can get huge easily. are you talking about a few thousand rows of vlookups? One way is to go to options and turn to manual calc. So it doesn't refresh and take up heaps of time if you are amending the xls. Press Shift+F9 to recalculate single sheet, or F9 to recalculate workbook.

OHH, now I see what he was saying... One thing, though, you need to have a workbook open to turn calculation to manual, so just make the option change in a blank workbook before opening up the large one. Otherwise, the large one will recalc as it is opening.
post #399 of 533
Quote:
Originally Posted by fairholme_wannabe View Post
Quick question regarding pivot tables--should be pretty easy but I can't figure it out.

Let's say I have a pivot table with rows of brands --> items within brand --> year and column of months

Formatted so that I can easily see YoY Lifts, etc per item. However, I would also like to see YoY Lifts per brand, yet because the year is a row as well, it rolls up all years per month on the brand subtotal. Therefore, for Brand A in Oct. I am getting Oct 2001-2010, as opposed to just a subtotal of Brand A for Oct 2010. I would like the brand to subtotal per month per year, just like the skus do.

Help?

Not sure I understand correctly - but it seems to me that this is just a matter of ordering the headings correctly (i.e. go the layout wizard and reverse the order of month and year).
post #400 of 533
I'm not sure there's a way to do what I want to do, but I'll go ahead and ask:

I have a Word document that contains a number of embedded Excel tables. This document is sent to other users who update the information in the tables and return it to me. I'm trying to find a way to make table A reference tables B and C. Basically, the user puts a bunch of numbers in tables B and C, and each table totals within itself. I then want those totals to automatically show up in table A as well.

Here's a bit of a kicker: The Word doc isn't mine, and I can't really make major updates to it (aside from tweaking the Excel tables embedded in it).

Does anyone have an idea on how to make this work?
post #401 of 533
Quote:
Originally Posted by MrG View Post
Here's a bit of a kicker: The Word doc isn't mine, and I can't really make major updates to it (aside from tweaking the Excel tables embedded in it).

This sounds just like the sort of problem I would have run into at my old job, with really kludgy cobbled-together solutions.

I messed around for a few minutes and I think I have something that might work, or you might be able to make it work better. I assume you're using Excel 2007? Try copying the cell you want to link to, then go to the other embedded spreadsheet, select the cell that you want linked, then do Paste Special or just go to the Paste button on the toolbar, and in the dropdown beneath it should be a Paste Link choice.

If you do Paste Special, on the left side of that window that pops up you should get a radio button choice between Paste and Paste Link. Select Paste Link and make sure the file format is Excel.

This seems to sort of work at first, but it also seems to be fragile and easy to break the link, but I can't figure out why. Here's an example of what the links look like, but I can't figure out the syntax: {=Excel.Sheet.12|'FILENAME'!'!_1352001847!Sheet1!R4C1'}
post #402 of 533
Ultimate n00b question: can anyone recommend a good Excel tutorial (online or maybe downloadable)? I only know the basics and want to learn more.
post #403 of 533
Quote:
Originally Posted by yerfdog View Post
This sounds just like the sort of problem I would have run into at my old job, with really kludgy cobbled-together solutions.

I messed around for a few minutes and I think I have something that might work, or you might be able to make it work better. I assume you're using Excel 2007? Try copying the cell you want to link to, then go to the other embedded spreadsheet, select the cell that you want linked, then do Paste Special or just go to the Paste button on the toolbar, and in the dropdown beneath it should be a Paste Link choice.

If you do Paste Special, on the left side of that window that pops up you should get a radio button choice between Paste and Paste Link. Select Paste Link and make sure the file format is Excel.

This seems to sort of work at first, but it also seems to be fragile and easy to break the link, but I can't figure out why. Here's an example of what the links look like, but I can't figure out the syntax: {=Excel.Sheet.12|'FILENAME'!'!_1352001847!Sheet1!R4C1'}



I think that worked. Many thanks!

Quote:
Originally Posted by Connemara View Post
Ultimate n00b question: can anyone recommend a good Excel tutorial (online or maybe downloadable)? I only know the basics and want to learn more.

Try mrexcel.com. I've never used it - most of my Excel knowledge came from school and experience - but one of my coworkers said it's very useful. If nothing else, she said it's free, so you could start there and see if it suffices.
post #404 of 533
Lynda.com has great tutorial videos. Check a torrent site and try to download some. Here, Conne: http://btjunkie.org/search?q=lynda+excel
post #405 of 533
D'oh. Correction to the above post: It worked, but the link includes the file path, which means it will only work with documents that don't move. This one has to be sent out via email and returned.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread