Import Named Ranges into Excel Document...HELP!!!

[WaylettChris]

Hi All,

Need some help! What I am trying to achieve is to be able to have a template excel document with a handful of Columns which I can populate with the details of named ranges I want to create. Then I want to import those named ranges into the workbook I have open at the time.

To illustrate I could Have column A as the name I wish the Range to Take, Some Columns where I type the range information which will make up the range, and a comment for the range to detail what it is...these are the boxes which appear when adding a named range manually...

I am asking this as I have a wide range of named ranges to create and this would save me a lot of time...

It would also be key that going forward, any named ranges that are already in the spreadsheet are left in, and the template merely adds to those...

Let me know if you know of a way this could be possible!

Thanks!!!

Chris

[Richard Buttrey]

Hi,

You'll need a macro to do this - untested but put this macro in your template workbook.

When the workbook into which you want to copy the range names is the ActiveWorkbook, Run the CopyNames macro from the Template workbook.

Please Login or Register  to view this content.

[WaylettChris]

Hi Richard,

Appreciate the feedback, and I think you understand what I want to do, but not necessarily how...

My understanding of your solution is that in the template document I will already have the named ranges set up...when actually I won't...

I will have the various bits of data in columns READY to create Named Ranges FROM (if that makes any sense?)

I have attached the template spreadsheet semi-finished (all columns are populated but I have many rows still to complete...

You will note that the sheet name EPL is referenced. There will be a sheet named this in the document where I want to create the named ranges.

Is there any way I can use the columns I have populated to create ranges in some way?

Thanks!



Chris

[Richard Buttrey]

Hi,

Yes this is possible but you'll still need a macro to read the names and cell addresses. However before finalising on this methodology are you sure using these range names is the best way of achieving your goal?

Whenever I see order in a requirement, as I see here with many of the addresses, I wonder if there is not some simple process.

Perhaps you could tell us what you have in your non template workbook, i.e. how it's laid out and what it contains - upload an example and in a note indicate 1.What names you would want
2. What cells they should cover (this might be the details you've already shown)
3. What you use the names for and
4. How you would use them if they were present, and if relevant manually add the result you expect to see after processing your data.

[WaylettChris]

Hi Richard Buttrey!

I appreciate your willingness to assist, and apologies for the delay in the response here.

To explain what I'm doing...I'm trialling a mathematical analysis tool of football match results to see if they have any correlation to future results. Something a group of mathematically minded friends are interested to investigate. How sad!

So, I have derived a Poisson Distribution which looks at previous results and means the average home goals, away goals etc, to try to predict the outcome of the next game.

I have attached two spreadsheets:

1. 'FOOTBALL Prediction Template vD2.xlsx' There are two tabs here, one called EPL which logs the results and does the poisson prediction for the next games. There is then a Tab which summarises the likelihood of each type of event occurring in all the games that day.
2. 'Names Ranges Import Template v1.xlsx' This is the spreadsheet of the named ranges I was hoping to import to make the Summary sheet easier, as rather than having loads of formulas it would use the named ranges

We would then use the summary tab to select the outcomes we think are possible, enjoy watching the games, and then see how close the actual result was to our prediction.


I have used manual formulas (=SUM Mainly) on the Summaries Tab of Spreadsheet 1 to show the result I am expecting if a named range were used instead as a check.

If there is an easier way of doing this then by all means let me know, but I think this will probably work okay for my purposes, just the importing of the names via a macro would be a lot easier than creating them all myself!

As a side note, within the macro could it be built in that if a named range exists already that we're trying to import...could it check the range, and if that differs, use the new range...if they're the same then just skip past?

Hope this all makes sense? Any questions let me know and I'd be happy to answer!




Chris