Macro to create new sheet template w/ cell references--is this possible?

[Scd1216]

Basically I'm trying to figure out if the following things are achievable using macros. If it's not possible, I'd greatly appreciate any suggestions you guys might have on work arounds and if it is possible I'd really appreciate any resources you could suggest that would help me learn how to build it.

So here's what I'm currently doing that I think could be done more efficiently with a macro:

Part of my job is to take 990 tax forms and manually input relevant data into excel. Mostly this means expense statements and statements of revenues. To do this I have a 990 template that I use. This template is on an individual sheet in the workbook and when I need to input a new 990, I copy a fresh version of the sheet. When I have inputted all of these 990s I then start finding the averages of different line items from each 990 sheet. So an example would be the median revenue from federal grants. To do this I create a summary sheet, do Median formula, and then click through all of the 990 tabs clicking on each federal grants cell. This takes an extremely long time because there are sometimes up to 50 990s and I do this for about 50-100 line items in total. I also do this for different combinations of 990s so I'll have Median Revenue from Federal Grants for institutions with < 50K annual visitors, <60K annual visitors, and so on and so fourth. It has gotten to the point where I open up a separate tab, paste the formula without the = sign, and cntrl replace all of the cell references with the new cell references corresponding to the new line item. It takes way too long and there are so many different clicks that I'm also really prone to making mistakes without realizing it.

Ideally, here is what I would like to do:

Have a button that when pressed a fresh 990 template is created on a new sheet (or on the same sheet if a new sheet isn't possible). This new 990 template would have nothing filled into it, but the cells would still be linked to my summary sheet formulas. So if I found a new 990 I would want to add I would press the button and the new template would be created. I would then fill in all the dollar amounts and while I'm filling in for example "Repairs and Maintenance" in the expense portion of the template, that specific dollar amount in automatically fed into my "Median Repairs and Maintenance" formula on the summary sheet.

Figuring out a way to have these new templates fed to the proper "median Repairs and Maintenance" cell on the summary sheet seems to be a lot trickier. Possibly an If Statement so that if the institution of that 990's attendance is greater than 50K it's "repairs and maintenance" is fed into "Median Repairs and Maintenance > 50K" on the summary sheet? I would of course have to do this for all the different line items as well, Repairs and Maintenance is just an example of one particular line item.

Anyways, let me know if there is something that I can expand on or explain more clearly. I really appreciate any help I can get.

 

[Fazza]

Hello.

One way to help, without going to macros.

If you set up a workbook with one worksheet called 'Start' and another called 'End' then position the worksheets on which you want to do the arithmetic between them. Then in another worksheet outside this range you can use formulas that will work across all the sheets between 'Start' and 'End', or whatever you call them if using different names.

Such as =MEDIAN(Start:End!A1)
or =SUM(Start:End!A1)

So this would easily handle one or one hundred data forms, and by inserting/removing worksheets it is simple to change.

OK?