Referencing External Cell VALUE

[BuyerRyan]

Ok, I've looked and looked, and so far no luck, so I'm hoping you can help. You'll also have to forgive my lack of the proper technical descriptions. So here we go:

I send out a weekly report that references a separate spreadsheet. The spreadsheet is basically a supply and demand broken out by weeks for thousands of items.

When I copy and paste from MY spreadsheet to the one I send out to my vendors (with their accompanying info), I get something that looks like this formula: ='[Ryan Purchasing.xls] Bulk Ingredients'!$P$20

Now, in order to drag this formula across several cells (by the lower right corner of the cell- not exactly sure what this is called) I have to remove the $ signs and then it will drag and continue to reference the corresponding cells. Gosh I hope this doesn't sound too confusing.

My question is this: Is it possible for the new spreadsheet to reference the cell VALUE of the other sheet, not the formula? The way it is now would work, but the recipients of the spreadsheet would have to have access to the first spreadsheet which they do not, and cannot for security reasons.

Again, I'm terribly sorry if this was written in an overly confusing manner. Just not sure what you may need to give me assistance.

Thank you for the help!! I hope my question makes sense!!

[Perfectly Safe]

The recipients are okay with the values from a linked spreadsheet as long as they don't try to update the values (Excel will ask them this when they open it). With that being said, you can copy your entire spreadsheet with the linked values, and then Paste Special - Values, which'll make all the formulas into...well...values.

Alternatively, you can go into Edit - Links and break the link to the source spreadsheet. This will have the same effect as pasting values.

Hope I've understood your problem correctly. I personally don't think you need to do either of these things, but it's good to do for neatness' sake.

[BuyerRyan]

Awesome. That is precisely what i was looking for. I figured there just HAD to be a way to do this.

Thank you so much for your quick and accurate answer. I appreciate the help!!