Index Formula HELP

**pattyb1626** · 07-05-2017, 04:35 PM

I have 200+ tabs in a workbook that each tab represents a route. Each route may have multiple rings on it, which is what I am looking for help with. I want to have a formula that will automatically find by what route, cell b1 on every individual tab, and then populate what ring(s) ride on that route. I attached an excel worksheet that I have highlighted in column C what I want the yellow highlighted cells in column A to show up when the formula is working correctly.

Originally, I was able to get the formula to work if all the information was on that same page by using the index formula (=IFERROR(INDEX($F$1:$J$1,SMALL(IF(INDEX($F$2:$J$2,1,0)="X",COLUMN($F$1:$J$1)-COLUMN($F$1)+1,""),1)),""). However, now that I changed the layout and have a masterlist with all information on it, I cannot get things to workout anymore. I guess, I am having issues having the formula correctly identify and matching the route to the correct information.
Please help me get a formula, especially in routes like 31.4 and 31.5 to work! I need the formula to pull from the 'Complete Route Info' tab.

Please and Thank you!!

**FlameRetired** · 07-05-2017, 05:30 PM

Try grouping the route sheets and array entering this formula in C6 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down until you get blanks.Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

With the sheets grouped the formula will copy through to all the grouped sheets.

If you aren't familiar with how to group sheets click the left most route sheet tab. While holding down the Shift key click the right most route sheet tab.

When finished right click on one of those tabs and click Ungroup sheets.

Index Formula HELP

LinkBack

Thread Tools

Rate This Thread

Display

Index Formula HELP

Re: Index Formula HELP

Thread Information

Users Browsing this Thread

Similar Threads

Index Match formula changing my Index daily

[SOLVED] New to INDEX...INDEX FORMULA NOT WORKING

[SOLVED] This formula works but I don´t understand why Index(Index) match

Index Match Formula, can index be fixed?

[SOLVED] IF & INDEX Formula =INDEX(D11:D59,COUNTA(D11:D59),1)

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Index Match Index Formula work slow

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions