The Excel Questions Thread

[rajesh06]

Originally Posted by Neo1824

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?

 

[Bhowie]

Originally Posted by Neo1824

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.

 

[Neo1824]

Originally Posted by Bhowie

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.

 

[rajesh06]

Originally Posted by Neo1824

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.

 

[Neo1824]

nope, no pivot tables in the workbook.

 

[ginlimetonic]

Originally Posted by Neo1824

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.

 

[fairholme_wannabe]

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?

 

[CunningSmeagol]

Originally Posted by ginlimetonic

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.

 

[rajesh06]

Originally Posted by fairholme_wannabe

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).

 

[MrG]

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?

 

[yerfdog]

Originally Posted by MrG

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'}

 

[Connemara]

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

 

[MrG]

Originally Posted by yerfdog

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!

Originally Posted by Connemara

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.

 

[StephenHero]

Lynda.com has great tutorial videos. Check a torrent site and try to download some. Here, Conne: http://btjunkie.org/search?q=lynda+excel

 

[MrG]

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.