Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting Named Ranges (2000)

    I have a workbook that contains a "template" worksheet, which in turn contains several named ranges e.g. rngA, rngB, rngC... I want to copy/ paste this template as a skeleton for new sheets in the workbook, corresponding to years - 2002, 2003, 2004, ... and then fill in the blanks, so to speak. I'd like corresponding named ranges to be present on new sheets as rngA_2004, rngB_2002, rngD_2006 etc.

    When I copy/ paste, the names don't carry across (as you'd expect), so I'm looking for a method of preserving the named range structure then assigning appropriate names corresponding to the year/ name of the particular worksheet, as described above. Hope that makes sense. Suggestions appreciated.

    Alan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting Named Ranges (2000)

    If you create named ranges: rngA, rngB, rngC in a sheet in the workbook and then create a copy of that sheet into that workbook. That new sheet will have a "local" copy of those exact names that are only good on that sheet but refer to the ranges on that sheet. Is that what you are after?

    Or are you trying to add new names for each new sheet. This would have to be done in code either by hardcoding it or by looking thru the named ranges and determining the "pattern" and creating new ones.

    What are you trying to do? Perhaps, there are alternatives.
    You could create 1 range name for each as a "general" and use indirect and offset to have it refer to range on a particular sheet based on some input (eg. year)

    I, personally, am not a proponent of creating yearly sheets: I would prefer all combined with an extra column for year if desired. Pivots, filters, subtotals, etc could handle most of the summary. You could even create sheet with a pulldown for year that "grabbed" all the yearly info from the database if desired.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Apart from STeve's excellent comments I would like to suggest you to download the Name Manager, by Charles WIlliams, Mathew Henson and myself:

    http://www.jkp-ads.com/NameManager.zip
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Hi Steve

    I think that your "local names" solution would work best in my context, if I could have made it to work. Your wording gave me the clue - "create a copy of that sheet". What I was doing was a Select All then a copy/ paste into an existing blank sheet. "Move or Copy Sheet..." does the trick. Thank you.

    I haven't had much experience working with data of this type, and would also tend to try to use the other approaches you suggest. But in this case, some of the named ranges appear directly as (linked) tables in a Word annual report, so there are layout issues as well. Another issue is that the size of these tables/ ranges will be different for each year. For instance, the table of TRANSACTIONS will vary according to bank account activity. The template only has 3 empty rows for this table and it will need to grow throughout each year. To accommodate this, I'll probably add menu items to insert cells correctly into a range.

    Thanks for the advice, as always.

    Alan

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Thanks Jan. I have a copy but haven't used it yet <img src=/S/grin.gif border=0 alt=grin width=15 height=15>... might be a good time to start!

    Alan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting Named Ranges (2000)

    Does the word table have to be "live" or is it a 1 time transfer?

    You could in excel create the table as needed (either via formulas or perhaps code) and paste it into word when needed. I don't see why it would have to be "live" in workd, you figure once you put 2002 and 2003 info in, it will never change and would not need to be linked. This years maybe but then just have a current year output that is live and "linkable".

    Only some suggestions, if you want more details, post back with more details of your setup and we can offer our evaluation of some options.

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Hi Steve

    You bring up some good points here. I can certainly see the value in your proposed approach to archived years - it would be nice to be able to use a pulldown to bring up data from a previous year on a single sheet. I've attached my "roughie" for comment and suggestions. The range names beginning with "min" are those linked to the Word doc. These are derivatives of the other "base" ranges: TRANSACTIONS, EXPENSES, INCOME - which are progressively filled over the course of a "working" year. (The INCOME range is still being worked out). The purple values are used for data validation. But there are a number of problems I foresee.

    Firstly, the data is not uniform from year to year. For instance, the EXPENSES range contains a descriptive field which contains freeform text. It might also contain any number of records, varying from year to year, as with the TRANSACTIONS and INCOME tables. Secondly, it may be necessary to have the current and previous year as "working years", since business from the year gone might not be closed off until some time into the next year.

    Throughout the working year, it will be necessary to add records (cells) into the working tables (ranges) as new entries are made. These new records will have to appear either above or below the existing records. The only way I know of doing this, without messing up formatting etc. and ensuring the range expands appropriately is to keep a blank record within the range to copy & paste as needed. This is a pretty messy way of doing things. Is there a better way?

    Any general suggestions also appreciated.

    Alan

    P.S. The Word links need to be "live" until the current year is "signed off". At this point, the links in the Word doc are broken and the XL dat for that year (and eventually the Word report) are archived away.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Alan,

    After looking at your attached workbook, I was a bit confused. It appears that you are attempting to keep a ledger of transactions with various Income and Expense categories for Units 1 thru.... Then you want to pull some data out to create reports

    If this is the case, then the attached might help as a starter.

    I tried to pull together a list of the various categories (see attached -- Sheet3) from the items you show in your template. I then used a single form: TRANSACTIONS (as seen on Sheet 2 attached) to create a database. This can be "filtered" by entering items in the "Search For" row. If you want to show all items from the year 2004, then enter <font color=blue>>12/31/03</font color=blue> under Date. Or, if you want just income items, enter <font color=blue>>0</font color=blue> under Credits

    Some items, such as Unit number or who is being reimbursed might be entered under the Details column.

    After filtering the data, you can re-format it into your Reports for Word.

    Hope this helps
    Paul

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Thanks for your workbook Paul. It looks to have some interesting features that I might be able to incorporate. Now that I see all of the data consolidated like that, I can see some appeal in such an organisation. I'm still not sure if all of the bookkeeping information can be readily "fitted up" like that, into a single table, since it comes from several independent sources e.g. Transactions is effectively an agglomeration of all issued electronic bank statements while Expenses include those things submitted by individuals for reimbursement. Although items may be duplicated in both, the two entries must appear for cross-checking/ book-balancing purposes. Still, it would be nice to be able to do it all in one table. Food for thought...

    Alan

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting Named Ranges (2000)

    SInce you don't have example data so I am not sure what you are doing exactly.

    I would imagine (no details worked out, as I don't understand completely) that if you set up your data in a "properly formatted" table or tables you could have excel create a pivot table, then you could read your "formatted output" from the pivot data.

    Could you provide an example of data and the output you expect from the data? I am still not clear on the question of WORD. What is the purpose of putting the data into word: is it constantly updating itself based on an excel table that changes constantly (which begs the question why not just view the table in excel) or is it a copy of the data (which instead of a link could be just copied and pasted into word when desired)

    Also I would have a different sheet for each data set: 1 for each data table and then 1 for each of the 3 outputs. It is much easier to manipulate and keep track of. You could have the outputs created only when needed: you could have the data summarized on the worksheet activate event so it is always up to date.

    The other question is: are the 2 data tables "related" in any way. They have several "columns" in common.

    If you are going to link, then you would also want the code run on the before save also, so the saved file is always up to date

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting Named Ranges (2000)

    Hi Steve

    I'm not sure that sample data would help in explaining (all aspects of) this. The more I look at it, the more I tend to think that the layout that's eventuated is more a reflection of the "unwritten" business rules by which this whole show operates (not of my design BTW).
    One example -

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting Named Ranges (2000)

    It might not explain all, but many of us here on the lounge have been known to be able to "extrapolate" and (even if we don't understand "completely") still answer questions and almost appear psychic <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    There are other things to consider:
    Just because you get data imported into a particular format, does not mean you have to store it that way. Routines for taking these files and converting to a more "datatable" format should not be difficult. If desired you could even create a routine to convert the selected data back into the format that you import.

    The thing that I like to stress, is to put the data into a format that is easiest to manipulate using the builtin routines or easier coding. This might involve using several tables and linking, but in the long run, for manipulation and calculations, you should be able to do most things you need now and also have a more adaptable workbook "application" in the future.

    I envision:
    Import data from whatever source/sources
    "translating" that info into the table or tables in your "workbook"
    "routines" to give the desired outputs
    "routine" to setup the links to word or just create a word report as needed from the data

    "translating" and "routines" could be formulas, builtin routines, macros, a combination depending on need.

    I can't be too much more specific, based on what you have provided, but I hope this helps. Please post back with more details if you need additional help.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •