Relative cell references not changing in an array

[JosieB]

Hi there

I'm trying to use an array formula to create a unique list of values from a longer list.

Please find example attached.

This is a formula I have used previously for another purpose. When you open the attachment you will see that there is an array in cells B4:B14 containing a relative cell reference which changes as you go down the range. You'll also notice that the formula works correctly.

However, if I re-enter this array (by entering the range and then pressing Ctrl+Shift+Enter) then the relative cell reference becomes fixed and the formula no longer works.

I'm pretty sure this is the same way I entered the array the first time (although it was a little while ago now) so I'm confused as to why it suddenley doesn't work.

Does anybody have any insight? Is there potentially something wrong with my settings or something?

Thanks in advance

Josie

[romperstomper]

You need to array enter it into one cell only, then copy that down.

[JosieB]

Hi romperstomper

Thank you so much! :-)

I'm slightly embarrassed that the answer was so easy! lol

Kind regards

Josie