Copy Formula with reference cells from every other column

[apurk45]

Hello
I want to copy formula to column to the right (across ~40 columns) on the calculation sheet for every two columns on the referencing sheet “Tax Brackets”. I’m aware that this can be accomplished using OFFSET and MOD function, but in this case, I have no idea how to modify my function to incorporate the OFFSET and/or MOD.
Any help will be greatly appreciated.

Here is the formula in col K

=IF(K30<'Tax Brackets'!O5,0,IF(K30<'Tax Brackets'!O6,(K30-'Tax Brackets'!O5)*'Tax Brackets'!P6,IF(K30>'Tax Brackets'!O6,(('Tax Brackets'!O6-'Tax Brackets'!O5)*'Tax Brackets'!P6))))

Regards,
John

 

[awoohaw]

What cell is the formula in? Are you saying you have 40 columns on the current sheet, and 80 columns on the 'Tax Bracket Sheet' (beginning at column O)? It also loooks like you are jumping ROWS as well on 'Tax Brackets' sheet. Would you be kind enough to post an xl2bb mini sheet (both sheets)? See link below for the add in.

 

[apurk45]

Formula is in K36 and needs to be copied to about 40 columns (cells) to the rights (L36, M36, N36, O36, P36, etc...)

WS "Tax Brackets" has this reference info spread across 80 columns. Here is a screenshot of just 16 columns
For now all these values in these 80 columns are the same - I am just setting up the template. Once I have all formulas and formatting done I will update the values.

 

[awoohaw]

If you are just setting this up. Then I suggest the tax bracket sheet to have only 3 columns: Year, MFI, and Rate. If you want to have a nice chart to show the brackets by year, you can have another pivot table with slicers or filters.

What do the yearly consecutive numbers in Row 4 mean? If needed, that would be a 4th column.

This is using the phillosopy of not mixing reports and data.

 

[awoohaw]

Here is what your lookup table and a a simple pivot report may look like:
(this assumes the values on Row 4 are superfluous).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Year	Something	MFI	Rate
3	2029	29	23,200	10%
4	2029	29	94,300	12%
5	2029	29	201,050	22%			Rate	Year
6	2029	29	383,900	24%			MFI	2029	2030	2031	2032
7	2029	29	487,450	32%			23,200	10%	10%	10%	10%
8	2029	29	731,200	35%			94,300	12%	12%	12%	12%
9	2029	29	10,000,000	37%			201,050	22%	22%	22%	22%
10	2030	30	23,200	10%			383,900	24%	24%	24%	24%
11	2030	30	94,300	12%			487,450	32%	32%	32%	32%
12	2030	30	201,050	22%			731,200	35%	35%	35%	35%
13	2030	30	383,900	24%			10,000,000	37%	37%	37%	37%
14	2030	30	487,450	32%
15	2030	30	731,200	35%
16	2030	30	10,000,000	37%
17	2031	31	23,200	10%
18	2031	31	94,300	12%
19	2031	31	201,050	22%
20	2031	31	383,900	24%
21	2031	31	487,450	32%
22	2031	31	731,200	35%
23	2031	31	10,000,000	37%
24	2032	32	23,200	10%
25	2032	32	94,300	12%
26	2032	32	201,050	22%
27	2032	32	383,900	24%
28	2032	32	487,450	32%
29	2032	32	731,200	35%
30	2032	32	10,000,000	37%
Sheet1