Preserve exact formula, monthly analysis for full year

[Sandler]

On a monthly basis, I run an analysis on a few entities based on certain criteria. To speed up the process, the analysis is driven by formula from the separate analysis tabs. This month I would create a May analysis driven by formulas. Above the May I have the same exact analysis tables for June-December (December being very top). After analysis is completed i can simply copy, paste value May. What’s the quickest way to copy the May formulas to June through December. I previously used the indirect function, which was better than nothing, but there has to be an even faster way for Excel to know that I want to preserve the same exact formulas.

 

[sheetspread]

What are you changing in the formulas? If it's the sheet name, then Indirect or ctrl-h (the only non VBA methods I can think of)

 

[Sandler]

I plan on not changing the formulas at all, since I am making copies of the tables a few rows above the original tables, the formulas will change via relative references. Maybe, if i used R1C1 reference style?

 

[sheetspread]

What about a named range?

 

[Sandler]

Perhaps, how would i go about using a named range to protect formulas?

 

[Sandler]

Sorry, i meant to say formulatext function not indirect function

 

[sheetspread]

You could write a lookup formula to a named range. Can you show an example of your data? Here's 1 way to do so:

https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813 (download the second folder)

and watch this useful video:

https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

 

[Sandler]

right now, i am flying through this pretty quickly by using the formulatext function. The issue is that it shows the formula until i either select the cell and hit enter or press F2 and hit enter. Is there a way to do that F2 part by selecting the whole thing instead of going cell by cell.

Notes

*	A	B	C	D	E
583	Ortho	*	*	*	*
584	*	*	*	*	*
585	=IF(+'Analysis ortho'!S41>0,"Favorable","Unfavorable")	*	to budget by *	=ROUND(+'Analysis ortho'!S41,-2)/1000	K
586	*	*	*	*	*
587	Amount	Expense	Fav/Unfav	*
588	=+'Analysis ortho'!V47	K	*Physician Salary *	=IF(+'Analysis ortho'!V47>0,"Favorable","Unfavorable")	*
589	=+'Analysis ortho'!V48	K	Staff Salary	=IF(+'Analysis ortho'!V48>0,"Favorable","Unfavorable")	*
590	=+'Analysis ortho'!V49	K	Incentive Accrual	=IF(+'Analysis ortho'!V49>0,"Favorable","Unfavorable")	*
591	=+'Analysis ortho'!V50	K	Incentive Adjustment	=IF(+'Analysis ortho'!V50>0,"Favorable","Unfavorable")	*
592	=+'Analysis ortho'!V51	K	Vacation Liability	=IF(+'Analysis ortho'!V51>0,"Favorable","Unfavorable")	*
593	=+'Analysis ortho'!V52	K	Other Benefits	=IF(+'Analysis ortho'!V52>0,"Favorable","Unfavorable")	*
594	=+'Analysis ortho'!V53	K	Physician Benefits	=IF(+'Analysis ortho'!V53>0,"Favorable","Unfavorable")	*
595	=+'Analysis ortho'!V54	K	Staff Benefits	=IF(+'Analysis ortho'!V54>0,"Favorable","Unfavorable")	*
596	=+'Analysis ortho'!V55	K	Malpractice	=IF(+'Analysis ortho'!V55>0,"Favorable","Unfavorable")	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:189px;"><col style="width:17px;"><col style="width:154px;"><col style="width:159px;"><col style="width:25px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[sheetspread]

The issue is that it shows the formula until i either select the cell and hit enter or press F2 and hit enter. Is there a way to do that F2 part by selecting the whole thing instead of going cell by cell.

Try ctrl-h replace = with =

If that doesn't work, highlight the range, alt-f11, ctrl-g, selection.formula = selection.formula

 

[sheetspread]

If that doesn't work, highlight the range, alt-f11, ctrl-g, selection.formula = selection.formula

(and then hit enter)