Referencing a different workbook (Updated w/Workbooks)

[Dynamo418]

Hi guys,

I have two workbooks. One is a balance sheet, and has a list of product #'s in column A, and a list of units corresponding to the product # in column B.

In the second workbook that I am creating, I have the same list of product #'s. I need to be able to input the sum of a group of units from workbook 1 into this new workbook, and have it be visible or 'saved' even when the first workbook is closed. For example:

If in dummy book 1 I have item #'s for the color 'red' for 6 sizes, I need to pull the sum of those 6 item #'s from dummy book 2 into cell C2 in book 1, and be there even if book 2 is not open.

Do I need to create a macro to do this, or is there a way to use a formula?

**I have uploaded dummy versions of the two workbooks. In book 1, I have highlighted in yellow where the sums should go. Item #'s are on the right side. In book 2, I have the item #'s on the left with corresponding unit balances. Let me know if that is unclear! **

Thanks,

[FDibbins]

see if this gives you what you need. adjust references as needed

[xladept]

Hi Dynamo418,

I got 5732 for Green:

Please Login or Register  to view this content.

[FDibbins]

xladept, the OP's value for green is correct

[xladept]

Yeah, you're right - I just checked it again.

[Dynamo418]

Thanks guys, that would appear to work. The issue I am having is the Vlookup reference cell from sheet 1 is a concatenation of 2 other cells, in 'general' format. So, i'm left with - for example - 214077789. I get an 'N/A#' message when trying to pull from the other sheet (also all in general format). However, if I type in 214077789 on the original sheet, I can pull the value from sheet 2. What's going on? I've tried switching the concat. & sheet 2 reference to number format, no luck .

[xladept]

Hi Dynamo418,

Did you try my code?

[Dynamo418]

I'm trying to... before I go through the process: the reference cells from the 2nd workbook are in 'number stored as text format' and cannot be changed. Is it even possible to use that code with that kind of format?

Thanks!

[xladept]

I don't know - they're in general format on the samples you posted - it's worth a try!

For some reason the finder stopped working - here's the code sans finder:

Please Login or Register  to view this content.