Way to reference cells other than relative or absolute when copying to another workbook

[kornstalker]

I would like to copy a range of rows from Book A (includes multiple formulas) to another workbook, Book B. I would continually be adding to Book B this way. In other words, there are multiple workbooks like Book A that I'd be pulling from and adding to Book B. Some of the formulas in Book A reference a certain cell F2. I want that reference to point to F2 from that Book A every time. I can't use absolute references b/c when I copy that range of rows to Book B, that absolute reference will now point to F2 at the very top of Book B, which is unrelated to the new range F2 that I want to refer to. I can't use relative references b/c when I try to copy that formula down the column of Book A, it wants to change my F2 reference to F3, F4, F5 and so on, as you go down the rows. Am I missing an obvious option, or is there some other method that would work for something like this?

[6StringJazzer]

...that absolute reference will now point to F2 at the very top of Book B, which is unrelated to the new range F2 that I want to refer to.

Not following this. I am reading this as you don't want it to point to F2 but you do want it to point to F2.

Exactly what do want this F2 reference to point to?

[kornstalker]

Well, I want it to point to the location of F2 from the Book A that it came from. So I want it "relative" to Book A, but "absolute" in terms of what cell I'm referring to in Book A. If I use absolute references, when I copy that into Book B, the absolute reference to F2 would not point to that same cell, b/c now that it's been pasted into Book B, F2 is something way above and unrelated to what was F2 in Book A. I'll work on attaching a couple example workbooks and see if that makes it easier to explain. I'm not sure that'll help anyone unless my explanation is understood first ha. Thanks

[mahju]

Try including sheet name with F2 as

A!$f$2
In your formula from where you are copying A,c,d etc

Regards

[kornstalker]

I've attached some samples that might make it more clear...

Mahju,

If I were pasting from Sheet A to Sheet B, what you're saying would work. But since I'm pasting from Book A to Book B, I didn't think that would work.

[mahju]

You may try to add the worksheet name as

Please Login or Register  to view this content.

Both worksheets should be open

Regards

[kornstalker]

I first CTRL + ` to make sure my selection from Book A would remain a formula, then selected the range in Book B to place the cells, then typed =, then selected the range in Book A, which was '[Book A.xlsm]Absolute #1'!$2:$5, then CTRL + SHIFT + ENTER to paste into Book B. That part worked, and moved formulas across. The problem is that it would not allow me to enter a date in the D2 cell which it needs to calculate the others due to ERROR:UNABLE TO CHANGE ARRAY or something along those lines.