Make a range-reference into once single cell?

Create a Named Range in Name Manager on the Formula tab.

Ideally, you could create a Dynamic Named Range to cater for increasing/decreasing range scope.


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

A Dynamic Named Range would look something like this:

Formula:

Please Login or Register  to view this content.

It's designed to cope with situations where the range can increase or decrease. However, this version relies on the range NOT having any blank entries. There are alternative versions that can cope with blank entries.

In your scenario, tim's solution looks like a good answer.

I have updated Tim's sample workbook to give you a worked example.