Excel Table - how to force inconsistent formulas

[illusionek]

Hello

I am working on a report and ideally I would like to use Excel table as the report will be constantly updated/new records added and I would like users to avoid dragging down formulas if the range extends so the Table would do the trick.

I need however to be able to do inconsistent formulas in the column for example Row 5 would be Vlookup, Row 6 SumIf etc the idea is that when new value gets added to the table then the same formulas would get repeated.

In the below example, lets assume I have only values A and B in Col1 and then I add C and I would like Vlookup and SumIf to get extended automatically.

I googled this but I can see only solutions on how to fix inconsistent formulas rather than other way around

Col1	Col2
A	Vlookup
A	Sumif
B	Vlookup
B	Sumif
C	Vlookup
C	Sumif

 

[shift-del]

Hi

I propose two solutions. I would recommend the second one which is more in line with a table.

Mappe10
	A	B	C	D	E	F	G
1	Col1	Col2			Col1	Col2	Col3
2	A	vlookup			A	vlookup	sumif
3	A	sumif			B	vlookup	sumif
4	B	vlookup			C	vlookup	sumif
5	B	sumif
6	C	vlookup
7	C	sumif
Tabelle1

Cell Formulas
Range	Formula
B2:B7	B2	=CHOOSE(MOD(ROW([@Col1]),2)+1,"vlookup","sumif")

 

[illusionek]

Could I make it work if I wanted to drag across as well?

I have oversimplified my example above as the table would expand in both directions.

The issue here is that the sum range in sumif would need to change accordingly as I am dragging formula to the right.

 

[shift-del]

Maybe a pivot table would be the best solution.
Or do it with Power Query.