cell references

K

Kelly McFaul

I hope I can explain this so that it makes sense.......... I have one sheet
(sheet1) with several rows and columns. There are two different sets of data
that goes in these columns, depending on different circumstances. I
duplicated the table that the first set of data is in to enter the second
set. In order to change between the data, I copy the first set, paste it to
another place on the sheet, then copy and paste the second set in it's
place.
That's not a problem....But, I have a second sheet (sheet2), that multiplies
the data on sheet1 and displays the result there.
The formula on sheet2 is "sheet1C48*$F$2" and is the same for all the other
rows D48*$F$2, E48*$F$2, etc.)"
When I switch around the data on sheet1, the reference on sheet2 changes to
wherever I paste the first set of data.
I need sheet2 to always use the range beginning with C48. I have tried
changing it to an absolute reference, but it still uses the data from the
first set, no matter where it is on the worksheet.
I hope this is clear, thanks in advance for any suggestions!
Kelly
 
M

Max

One way ..

In Sheet2,

Instead of say, in G2: =Sheet1!C48*$F$2,
with G2 copied across

Put in G2:
=OFFSET(INDIRECT("Sheet1!C48"),,COLUMN(A1)-1,)*$F$2
Then copy G2 across

The formulas in G2, H2, I2 will now
always point to the correct cells in Sheet1, viz.:

Sheet1!C48*$F$2,
Sheet1!D48$F$2,
Sheet1!E48$F$2, etc

And if you want it to increment going down instead of across, just change
the COLUMN to ROW in the starting cell formula in G2, viz,:

Put instead in G2:
=OFFSET(INDIRECT("Sheet1!C48"),,ROW(A1)-1,)*$F$2
Then copy G2 down

G2, G3, G4 will always return:

Sheet1!C48*$F$2,
Sheet1!D48$F$2,
Sheet1!E48$F$2, etc
 
H

HS Hartkamp

I think you might need the index function. Your situation is data changing
where you don't want references to that data changing as well. This can be
achieved with either the index function or the indirect function.

I prefer index, and put a column of row-numbers and a row of column-numbers
around my block to use as parameters for my function. That way, you can
still copy or drag the formulas.

Bas Hartkamp
 
Top