Copy cells (columns) from workbook to workbook, then "flatten" the file?

[robino]

Hi Everyone,

I have a little bit of experience with basic Excel functions, but have a stumper (for me at least).

I export data from my inventory program to an excel spreadsheet called "c1_sty010.xls." This contains Style Numbers in a column, Colors in another column, Description in another, Sizes, inventory, etc.

I want to update a different Workbook with that information automatically. I am able to reference the cell in the source file, but it won't copy that formula sequentially down the column when I try to pull the little cross down, whether or not I hold CTRL. So, I have to manually change the cell reference for each subsequent cell in the column. Is there a better way to do this?

Also, when all the information is in the destination file, I want to be able to send it to my customer with only the data in the cells, not the formulas, which is what I mean by "flattening" the file.

Okay, here's all of you now... ...and me:

I know, it's probably an unbelievably stupid question with a simple answer, but can anyone help me?

Thanks!
Robin

 

[Jerry Sullivan]

Welcome to the Board Robin!

To copy down your formulas "sequentially" use, relative references:
='[Monthly Report.xls]Sheet1'!A1 or
='[Monthly Report.xls]Sheet1'!$A1

instead of absolute references:
='[Monthly Report.xls]Sheet1'!$A$1

To "flatten" your file, use Copy then PasteSpecial > Paste: Values

 

[robino]

Hey Jerry,

First of all, a sincere thank you for taking time out of your day to help me out. Your effort is really appreciated! Second of all, you totally saved my day! It works great. I was using the absolute reference, and you nailed it.

Again, much appreciated --I have become addicted to reading the posts here, and am learning so much. Wish I had come here years ago!

Sincerely,
Robin

 

[Jerry Sullivan]

Hi Robin,

Thank you for your kind feedback. Yes, it's a great place to learn.
Hope you continue to be an active member.

 

[robino]

I'll definitely be coming back. I think the main problem with searching for an answer here is actually the sheer volume of posts! I also did not know the correct terminology to use either, as in "absolute reference," etc.

But, I'll keep reading and learning. Excel is pretty amazing software

thx,
Robin