Fairly New To Excel - Is This Possible In Excel?

[zsloane]

Ok, so I have have used Excel before, like most people, for basic data logging and calculations for school. However, I am just getting started with actually using the more advanced abilities of Excel. I have been working on this spreadsheet for work. Most of what I am doing is prett simple. My issue is what happens after I'm gone, since this is only a temporary summer job for me. I have much more than this, but for simplicity sake, let's assume I have one sheet with one formatted table that is basically copied and pasted vertically as new "sets" of data are gathered. The table has some defined names in it that are used to display the few calculated cells on another sheet as sort of an overview.

I have no problem copying and pasting the table and adding new defined names for the new table, myself, since I was the one that designed it and I can do it fairly quickly. However, this spreadsheet will potentially be kept and added to after I am gone. So, my question for you guys is therefore, is there a way to sort of make a template object or something out of the group of cells that is the table (hopefully with the defined names, but if not thats fine I guess) so that someone down the line could just add a new table and fill in the data where necessary and be done with it? If this could only be done with script, then I'm not going to bother because that's not an option.

Sorry if this is a very naive and impossible idea.

Thanks

 

[Yogi Anand]

Hi zsloane:

Welcome to Mr Excel Board!

I suggest you post a small illustration with some sample data and your expected result(s) along with any needed explanation as to the logic -- so the viewers on the Board can see what you are working with and what you are trying to accomplish.

 

[zsloane]

Alright, basically what I am trying to do is say for example I have a simple four celled table. Top two are column headers, and bottom just contain a number or something. Each of the numbers is a defined name, Data1 and Data2 maybe for example, which are used to display the information somewhere else. Now what I would love to be able to do is to somehow save the whole four celled table as sort of a template so to speak that could be just copied and pasted in when necessary.

I doubt it, but if the defined names could be copied as well, that would really be what I'm interested in. Since, I suppose if I just wanted the table, I could just put it in a separate sheet and copy and paste it in whenever I need to.

Any help at all would be greatly appreciated.

 

[mikerickson]

If your table is in A1:B2, I wouldn't give a name to B2. What I would do is use a dynamic named range that accepts inserted columns.

Lets move the data range to B3:D4 and create this dynamic named range.
Name:myRange
RefersTo: =OFFSET($B$3,0,0,COUNTA($B:$B)-COUNTA($B$1:$B$2),COLUMNS($B$3:$C$3)

Columns can be added above or to the left of the data and the range will adjust. New Columns can be inserted in the data range, and the last term will automaticaly adjust to the new size of the data.

Formulas like INDEX(myRange,2,1) would replace the current names that you have given each cell. (INDEX can also return an entire row (or column) of the range.)

 

[zsloane]

Alright, so I have a new question that could help to do what I'm looking for.

In the example of having three sheets (Display, Data1, Data2), if Data1 and Data2 are both set up exactly the same except for the actual numbers, is there a way to have a defined name/range so that on the Display sheet, when I type ='Data1'name it shows the cell thats defined from that sheet, and when I type ='Data2'name, it gives the same cell from the Data2 sheet instead without just setting up Data1 completely and then copying the sheet like I have seen some people suggest?