INDEX to replace OFFSET

hi there. it looks like your OFFSET can remove this step:
MATCH("Flow Delivery",OFFSET(Pivot!$A:$A,,MATCH("Flow Delivery",Pivot!$6:$6,0)-1),0)
if you are unsure where the headers are at, you could not possibly use the part in red above. since you already know it's at row 6, then simply:
=OFFSET(Pivot!$A$1,6,MATCH("Flow Delivery",Pivot!$6:$6,0)-1,1000)

to do in INDEX would be:
=INDEX($6:$6,MATCH("Flow Delivery",Pivot!$6:$6,0)):INDEX($1:$1048576,1000,MATCH("Flow Delivery",Pivot!$6:$6,0))

if you change the ranges in the OFFSET from full column/rows to the potential max columns/rows you think you will need, it should speed things up (Im assuming speed is the issue here?)

I understand, but reducing the range will reduce the calcs

using a formula to find a row you are not sure where isn't going to be very efficient either. you might want to consider VBA? this code here simply finds the row and column of "Flow Delivery" using Find function (CTRL + F). it then changes your existing Named Range.

Please Login or Register  to view this content.

you have a few options of where to place it, depending on how your data is like. you could choose to activate:
1. when workbook is open
2. when "Pivot" worksheet is selected
3. whenever you want to change it.
4. etc.

i will just show for option 1-3. First, press ALT + F11 to open VBE. press CTRL + R to ensure you have Project Explorer on the left.
Option 1. Double-click ThisWorkbook on the Project Explorer. There is a dropdown near the middle named "General". find Workbook. the default on the right will become Open. Paste the code in between "Private Sub Workbook_Open()" and "End Sub", looking like this:

Please Login or Register  to view this content.

you can view the dropdown on the right for more options.

Option 2. Double-click Pivot on the Project Explorer. There is a dropdown near the middle named "General". find Worksheet. the default on the right will become SelectionChange. Change it to Activate. Paste the code in between "Private Sub Worksheet_Activate()" and "End Sub", looking like this:

Please Login or Register  to view this content.

Option 3.[*]Choose Insert > Module[*]Edit > Paste the macro into the module that appeared with a sub name like this:

Please Login or Register  to view this content.

[*]Close the VBEditor[*]Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)[/list]
To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Row:

Please Login or Register  to view this content.

column:

Please Login or Register  to view this content.