I have spent much time this morning trying to find the answer to my question. So far no luck. Maybe you can help.

I have 2 sheets in a workbook.
Sheet 1 is called PChart.
Sheet 2 is called Defect Sheet.

Defect Sheet, Cell E8 has a value of 5. Cell E9 has a value of 10.
In the Sheet called PChart, I want to write a formula that references the values in row 8.

If I type the following formula ='Defect Sheet'!E$8, I will get the result of 5. This is correct.

The problem is that if I try to copy this formula down the column on the PChart Sheet, I get the same results of ='Defect Sheet'!E$8, I will get the result of 5.

This isn't what I am after. I want the formula to be ='Defect Sheet'!E$8 for the first row and on the next row for it to be ='Defect Sheet'!F$8.

Thanks
Barry Givens
bgivensatasmo-na.com

--
Message posted via http://www.officekb.com

Do you really want to get F8? Or do you want to get E9, the reason I am
asking is that you write that you have 5 in E8 and 10 in E9 but then you
refer to F8. I assume you made a typo and want what's in E9, E10 and so on.
Just remove the dollar sign before the row number, i.e.

='Defect Sheet'!E8


and copy down. If indeed you meant that you will copy down but you want to
increment across please post back

Regards,

Peo Sjoblom

"Barry Givens via OfficeKB.com" wrote:

I have spent much time this morning trying to find the answer to my question. So far no luck. Maybe you can help.

I have 2 sheets in a workbook.
Sheet 1 is called PChart.
Sheet 2 is called Defect Sheet.

Defect Sheet, Cell E8 has a value of 5. Cell E9 has a value of 10.
In the Sheet called PChart, I want to write a formula that references the values in row 8.

If I type the following formula ='Defect Sheet'!E$8, I will get the result of 5. This is correct.

The problem is that if I try to copy this formula down the column on the PChart Sheet, I get the same results of ='Defect Sheet'!E$8, I will get the result of 5.

This isn't what I am after. I want the formula to be ='Defect Sheet'!E$8 for the first row and on the next row for it to be ='Defect Sheet'!F$8.

Thanks
Barry Givens
bgivensatasmo-na.com

--
Message posted via http://www.officekb.com

You are write, it was a typo. I can not just copy it down, if I do that the column will stay the same, and the row will change.

I need for the referenced column to change and the row to remain the same.

The results should be.
=E8 and the result should be 5
=F8 and the result should be 10 or whatever the next value is.
The values are listed in portrait on the one sheet and the PChart sheet that references it is in landscape. That is where I am haveing the trouble.
Thanks

--
Message posted via http://www.officekb.com

I have the answer. This seems to work for me.
=OFFSET('Defect Sheet'!E$8,0,ROW(E1)-1)

Thanks For your help.

--
Message posted via http://www.officekb.com