Issue with Referencing when using either $$ or not.

[TheWannabe]

I have a 2 worksheets, worksheet A and worksheet B. In the first worksheet I am keeping all of the data for each week. Every week I have a macro that will insert a new line in worksheet A.
By inserting this new line it means that the information I need for calculations will always be in B5.
In worksheet B I have the following command (=SheetA!$B$5), I have also tried (=SheetA!B5).
If I insert a new row above row 5 which now updates the current B5 cell to be blank. (Data will be manually put into it).
In worksheet B the command =SheetA!$B$5 will automatically update to =SheetA!$B$6 to follow the data even though I want it to stay as =SheetA!$B$5.
The same thing happens if I don't use the absolute referencing with =SheetA!B5.
I have also tried dragging the information down, or cutting it and pasting it in the row below, yet the formula still follows the data rather then staying on the specific cell.

Is there an option that I need to check for this or is it not possible?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this formula in Sheet B does what you want.

=INDEX(SheetA!B:B,5)

 

[mohadin]

Hi
And welcome
Try

=INDIRECT("SheetA!"&ADDRESS(5,2))

 

[TheWannabe]

Welcome to the MrExcel board!

See if this formula in Sheet B does what you want.

=INDEX(SheetA!B:B,5)

Hi Peter,
Thank you so much, that is exactly what I wanted.

 

[Peter_SSs]

Hi Peter,
Thank you so much, that is exactly what I wanted.

You're welcome. Thanks for the follow-up.

For the record, an INDIRECT function formula could also work but being a volatile function INDIRECT is best avoided when there are simple non-volatile options available.

 

[TheWannabe]

You're welcome. Thanks for the follow-up.

For the record, an INDIRECT function formula could also work but being a volatile function INDIRECT is best avoided when there are simple non-volatile options available.

Perfect I will look into them for the future but use the =INDEX(SheetA!B:B,5) for now.

 

[TheWannabe]

You're welcome. Thanks for the follow-up.

For the record, an INDIRECT function formula could also work but being a volatile function INDIRECT is best avoided when there are simple non-volatile options available.

Is there would be a way to use the INDEX function with the SUM function? I was trying to go SUM(INDEX(SheetA!B5:E5)).

This would be to get SUM all numbers in a singular row, but when I inserted a new row, it went and followed that data to row 6 rather then stay on row 5.

 

[Fluff]

It would need to be

SUM(INDEX(SheetA!B:E,5,0))

 

[TheWannabe]

It would need to be

SUM(INDEX(SheetA!B:E,5,0))

Thats perfect, thank you very much

 

[Fluff]

Glad to help & thanks for the feedback.