Function to ignore hidding or previous columns

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.

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

<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>
</tbody>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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, ...
 

DisplayName

New Member
Joined
May 24, 2017
Messages
10
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. :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted. Thanks for letting us know. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,135
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top