Transpose - but with formula - Paste across, but move cell references down & over one

[jdawson1237]

Can anyone help me out here. I'm dealing with a pretty messy Cohord Analysis and I need to be able to paste across a row, but have the cell references move over one and down one each time I paste to the right.

Does that make sense?

Said another way, normally when you paste to the right, it moves your cell reference over one to the right.

How do I make it go down one as well while pasting on the same row?

Thanks in advance!

 

[Jon21990]

Have you tried using an offset with a counta? you should be able to offset down by the amount amount of non blank cells before it (you'll have to freeze the first half of the counta and leave the second half dynamic)... Do you have any references? It's difficult to write something when I don't know how your sheet looks!

Jon

 

[Jonmo1]

Try this

=OFFSET(A$1,COLUMNS($A1:A1)-1, 0)

The A$1 is the first cell you want to reference, thend drag the formula to the right.
Don't change the COLUMNS($A1:A1) reference, it's not related to your data.

 

[jdawson1237]

Try this

=OFFSET(A$1,COLUMNS($A1:A1)-1, 0)

The A$1 is the first cell you want to reference, thend drag the formula to the right.
Don't change the COLUMNS($A1:A1) reference, it's not related to your data.

That works! Thanks so much. I've tinkered around with OFFSET but never really got it to work properly.

How do I insert an image I have saved on my harddrive?

 

[Jonmo1]

You're welcome.

Don't attach pictures, they're really not helpful because we can't copy/paste data from it into our own books for testing.
Insted, try using an HTML tool that can make a nicel formatted table that you can post into the forumn.

Try either Excel Jeanie or MrExcel HTML Maker from the recommended addins thread
Recommended Addins

 

[jdawson1237]

You're welcome.

Don't attach pictures, they're really not helpful because we can't copy/paste data from it into our own books for testing.
Insted, try using an HTML tool that can make a nicel formatted table that you can post into the forumn.

Try either Excel Jeanie or MrExcel HTML Maker from the recommended addins thread
Recommended Addins

Like so?

[B]Excel 2013/2016[/B][TABLE]
<colgroup><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]18[/TD]
[TD="align: right"] 287,531 [/TD]
[TD="align: right"] 557,879 [/TD]
[TD="align: right"] 253,885 [/TD]
[TD="align: right"] 86,135 [/TD]
[TD="align: right"] 28,442 [/TD]
[TD="align: right"] 10,714 [/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"] 348,598 [/TD]
[TD="align: right"] 654,489 [/TD]
[TD="align: right"] 229,733 [/TD]
[TD="align: right"] 52,493 [/TD]
[TD="align: right"] 20,120 [/TD]
[TD="align: right"] 11,623 [/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"] 495,944 [/TD]
[TD="align: right"] 668,558 [/TD]
[TD="align: right"] 198,923 [/TD]
[TD="align: right"] 41,829 [/TD]
[TD="align: right"] 24,881 [/TD]
[TD="align: right"] 13,210 [/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"] 429,890 [/TD]
[TD="align: right"] 724,989 [/TD]
[TD="align: right"] 166,207 [/TD]
[TD="align: right"] 47,562 [/TD]
[TD="align: right"] 27,699 [/TD]
[TD="align: right"] 10,783 [/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"] 460,158 [/TD]
[TD="align: right"] 610,072 [/TD]
[TD="align: right"] 171,620 [/TD]
[TD="align: right"] 55,854 [/TD]
[TD="align: right"] 27,688 [/TD]
[TD="align: right"] 7,601 [/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"] 431,808 [/TD]
[TD="align: right"] 562,231 [/TD]
[TD="align: right"] 209,937 [/TD]
[TD="align: right"] 75,392 [/TD]
[TD="align: right"] 72,958 [/TD]
[TD="align: right"] 19,640 [/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"] 363,237 [/TD]
[TD="align: right"] 531,263 [/TD]
[TD="align: right"] 227,042 [/TD]
[TD="align: right"] 195,106 [/TD]
[TD="align: right"] 33,891 [/TD]
[TD="align: right"] 20,294 [/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: right"] 418,875 [/TD]
[TD="align: right"] 544,967 [/TD]
[TD="align: right"] 323,118 [/TD]
[TD="align: right"] 60,172 [/TD]
[TD="align: right"] 24,951 [/TD]
[TD="align: right"] 10,105 [/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: right"] 620,303 [/TD]
[TD="align: right"] 664,577 [/TD]
[TD="align: right"] 165,104 [/TD]
[TD="align: right"] 36,512 [/TD]
[TD="align: right"] 16,757 [/TD]
[TD="align: right"] 7,147 [/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Inland CV[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!22:22,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!23:23,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!24:24,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!25:25,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!26:26,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I18[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A18,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!23:23,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!24:24,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!25:25,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!26:26,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I19[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A19,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!24:24,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!25:25,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!26:26,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I20[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A20,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!25:25,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!26:26,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I21[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A21,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!26:26,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I22[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!31:31,MATCH([COLOR=Red]$A22,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!27:27,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!31:31,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I23[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!32:32,MATCH([COLOR=Red]$A23,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!28:28,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!31:31,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!32:32,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I24[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!33:33,MATCH([COLOR=Red]$A24,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!29:29,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!31:31,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!32:32,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!33:33,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I25[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!34:34,MATCH([COLOR=Red]$A25,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!30:30,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!31:31,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!32:32,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!33:33,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!34:34,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I26[/TH]
[TD]=INDEX([COLOR=Blue]'Inland Waterfall'!35:35,MATCH([COLOR=Red]$A26,'Inland Waterfall'!$5:$5,1[/COLOR])[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jdawson1237]

Thanks for the info. One more question. How would I apply the OFFSET to something like this?

Last 6 month average from data above. Each time I copy/paste to the right, I need the cells averaged to move up one.

Let's see if I can get the html from MrExcel to work... [B]Excel 2013/2016[/B][TABLE]
<colgroup><col><col><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]173[/TD]
[TD="align: center"]18.4%[/TD]
[TD="align: center"]13.2%[/TD]
[TD="align: center"]3.3%[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Inland CV[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D173[/TH]
[TD]=AVERAGE([COLOR=Blue]D155:D166[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E173[/TH]
[TD]=AVERAGE([COLOR=Blue]E154:E165[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F173[/TH]
[TD]=AVERAGE([COLOR=Blue]F153:F164[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Pretty much the same way, just add the average function, and make the offset number a negative..

=AVERAGE(OFFSET(D$155:D$166,1-COLUMNS($A1:A1), 0))

 

[jdawson1237]

Pretty much the same way, just add the average function, and make the offset number a negative..

=AVERAGE(OFFSET(D$155:D$166,1-COLUMNS($A1:A1), 0))

Ughh for the life of me I cannot get this to work the way I need it to. I know this would be an easy fix for one of you experts. Thanks for taking the time to help me out though.

 

[jdawson1237]

I don't understand why it's 1-COLUMNS when i need the calculated array to move up one row each time I copy/paste the formula to the right.