Named Range List with Corresponding Values

[shoulddt]

Hello,
I have a workbook that has around 1,000 named ranges of varying sizes. I need to create a list of the named ranges and their values. I know you can use the Paste List feature to paste the names and location, but I cannot find a way to get the ranges' values. What would be the best way to accomplish this? I foresee it being a problem that some are single cell, some are 1x3, and some are 3x3 ranges. Any ideas or help is much appreciated. If all else fails I can do it manually, but that would take a while with as many named ranges as I have.

Thanks,
Dustin

[jeversf]

Any chance of a sample file Dustin?

[FlameRetired]

Dustin if you are not familiar with how to do this (Forget the attachment button. It hasn't worked for a while.)

To attach a file to your post,

1. click “Go Advanced” (next to Post Quick Reply – bottom right),
2. scroll down until you see “Manage Attachments”, click that,
3. click “Browse”.
4. select your file(s)
5. click “Upload”
6. click “Close window”
7. click “Submit Reply”

The file name will appear at the bottom of your reply.

Please be sure to upload an Excel workbook file and not pictures or screen shots. They are pretty much useless. Oh, and be sure to desensitize the data.

Does this help?

[shoulddt]

Hey guys,
Here is a simplified version of my program. It has a lot less named ranges, but should serve the purpose of an example file. I just need a column in the Save List tab of all the named ranges and then their corresponding values.

[FlameRetired]

Since I don't have access to some of the file references in the definitions I don't know how this will work at your end. It works with named ranges within the upload.

1. Select B1:B921 in 'Save List'.
2. While holding down the Ctrl key hit H.
3. This will open up the find and replace dialogue.
4. Type the = sign in Find what
5. Type the = sign again in Replace with
6. Click Replace all
7. Yup! Believe it or not Excel has to be "reminded" that these are formulas.
8. In-workbook definitions will work without a hitch.
9. A dialogue box may open for external files.
10. You will have to "fiddle" with this part to see what works.
11. Since I do not have the files I get #REF! errors.

I am curious to know how this works for you. Please let me know.

[shoulddt]

Very nice solution FlameRetired. I would have never thought of something that simple. I did have to go through and insert some rows for the named ranges of different sizes, but it wasn't too bad. Thanks for the help guys!

[FlameRetired]

Thanks for the feedback and the rep. I came across that strategy in a book by Bob Umlas.