Function to ignore hidding or previous columns

D

DisplayName

New Member
Joined
May 24, 2017
Messages
10
Hello - I have data in range J3:v8. And another range from Y3:AV8. I want to subtract J3 from Y3, in columnd Z3, and K3 from AA3 in column AB3; essentially skipping the column before the range y3:av8. I've tried index columns, and offset(columns) function, and I just can't figure it out.

[TABLE="width: 2689"]
<colgroup><col span="12"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD]GL[/TD]
[TD][/TD]
[TD][/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD]Invoice[/TD]
[TD]Adjustment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD]Total[/TD]
[TD]Variance[/TD]
[TD][/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Jan-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Feb-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD] 1,529.53[/TD]
[TD] 1,599.05[/TD]
[TD] 1,390.48[/TD]
[TD] 1,599.05[/TD]
[TD] 2,123.89[/TD]
[TD] 1,469.91[/TD]
[TD] 1,826.89[/TD]
[TD] 1,588.60[/TD]
[TD] 1,668.03[/TD]
[TD] 1,747.46[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,542.89[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 1,530[/TD]
[TD] - [/TD]
[TD] 1,599[/TD]
[TD] - [/TD]
[TD] 1,390[/TD]
[TD] - [/TD]
[TD] 1,599[/TD]
[TD] - [/TD]
[TD] 2,124[/TD]
[TD] - [/TD]
[TD] 1,470[/TD]
[TD] - [/TD]
[TD] 1,827[/TD]
[TD] - [/TD]
[TD] 1,589[/TD]
[TD] - [/TD]
[TD] 1,668[/TD]
[TD] - [/TD]
[TD] 1,747[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,543[/TD]
[/TR]
[TR]
[TD] 1,232.33[/TD]
[TD] 1,288.35[/TD]
[TD] 1,120.30[/TD]
[TD] 1,288.34[/TD]
[TD] 1,056.00[/TD]
[TD] 1,108.80[/TD]
[TD] 1,324.80[/TD]
[TD] 1,152.00[/TD]
[TD] 1,209.60[/TD]
[TD] 1,267.20[/TD]
[TD] 1,324.80[/TD]
[TD] 1,152.00[/TD]
[TD] 14,524.52[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 1,232[/TD]
[TD] - [/TD]
[TD] 1,288[/TD]
[TD] - [/TD]
[TD] 1,120[/TD]
[TD] - [/TD]
[TD] 1,288[/TD]
[TD] - [/TD]
[TD] 1,056[/TD]
[TD] - [/TD]
[TD] 1,109[/TD]
[TD] - [/TD]
[TD] 1,325[/TD]
[TD] - [/TD]
[TD] 1,152[/TD]
[TD] - [/TD]
[TD] 1,210[/TD]
[TD] - [/TD]
[TD] 1,267[/TD]
[TD] - [/TD]
[TD] 1,325[/TD]
[TD] - [/TD]
[TD] 1,152[/TD]
[TD] 14,524[/TD]
[/TR]
[TR]
[TD] 1,380.81[/TD]
[TD] 1,443.57[/TD]
[TD] 1,255.28[/TD]
[TD] 752.37[/TD]
[TD] 2,433.34[/TD]
[TD] 753.16[/TD]
[TD] 1,443.58[/TD]
[TD] 1,255.28[/TD]
[TD] 1,318.04[/TD]
[TD] 1,380.81[/TD]
[TD] 1,443.57[/TD]
[TD] 1,255.28[/TD]
[TD] 16,115.09[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 1,381[/TD]
[TD] - [/TD]
[TD] 1,444[/TD]
[TD] - [/TD]
[TD] 1,255[/TD]
[TD] - [/TD]
[TD] 752[/TD]
[TD] - [/TD]
[TD] 2,433[/TD]
[TD] - [/TD]
[TD] 753[/TD]
[TD] - [/TD]
[TD] 1,444[/TD]
[TD] - [/TD]
[TD] 1,255[/TD]
[TD] - [/TD]
[TD] 1,318[/TD]
[TD] - [/TD]
[TD] 1,381[/TD]
[TD] - [/TD]
[TD] 1,444[/TD]
[TD] - [/TD]
[TD] 1,255[/TD]
[TD] 16,115[/TD]
[/TR]
[TR]
[TD] 1,619.20[/TD]
[TD] 1,692.80[/TD]
[TD] 1,472.00[/TD]
[TD] 1,692.80[/TD]
[TD] 1,619.20[/TD]
[TD] 952.39[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1,872.62[/TD]
[TD] 1,910.77[/TD]
[TD] 1,661.54[/TD]
[TD] 14,493.32[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 1,619[/TD]
[TD] - [/TD]
[TD] 1,693[/TD]
[TD] - [/TD]
[TD] 1,472[/TD]
[TD] - [/TD]
[TD] 1,693[/TD]
[TD] - [/TD]
[TD] 1,619[/TD]
[TD] - [/TD]
[TD] 952[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1,873[/TD]
[TD] - [/TD]
[TD] 1,911[/TD]
[TD] - [/TD]
[TD] 1,662[/TD]
[TD] 14,494[/TD]
[/TR]
[TR]
[TD] 1,974.72[/TD]
[TD] 2,064.48[/TD]
[TD] 1,795.20[/TD]
[TD] 2,064.48[/TD]
[TD] 1,974.72[/TD]
[TD] 1,884.96[/TD]
[TD] 2,208.00[/TD]
[TD] 1,920.00[/TD]
[TD] 2,016.00[/TD]
[TD] 2,112.00[/TD]
[TD] 2,208.00[/TD]
[TD] 1,920.00[/TD]
[TD] 24,142.56[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] 1,975[/TD]
[TD] - [/TD]
[TD] 2,064[/TD]
[TD] - [/TD]
[TD] 1,795[/TD]
[TD] - [/TD]
[TD] 2,064[/TD]
[TD] - [/TD]
[TD] 1,975[/TD]
[TD] - [/TD]
[TD] 1,885[/TD]
[TD] - [/TD]
[TD] 2,208[/TD]
[TD] - [/TD]
[TD] 1,920[/TD]
[TD] - [/TD]
[TD] 2,016[/TD]
[TD] - [/TD]
[TD] 2,112[/TD]
[TD] - [/TD]
[TD] 2,208[/TD]
[TD] - [/TD]
[TD] 1,920[/TD]
[TD] 24,142[/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 5,043.72[/TD]
[TD] 3,996.93[/TD]
[TD] 9,040.65[/TD]
[TD] - [/TD]
[TD][/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 5,044[/TD]
[TD] - [/TD]
[TD] 3,997[/TD]
[TD] 9,041[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
See if this does what you want.
In Z3: =Y3-INDEX($J3:$U3,COUNTIF($Z$1:Z$1,"Invoice"))

Now copy that down column Z and then copy the column Z formulas to columns AB, AD, ...
 
Upvote 0
Thank you! It didn't quite work out, BUT I realized I could do simple index match function, using the month as the criteria, and pasting the formula in each column. :)
 
Upvote 0
Glad you got it sorted. Thanks for letting us know. :)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top