If and Look Up Functions across mutiple tabs on workbook

**JMillyard** · 05-31-2013, 12:34 PM

I have a fairly large workbook of 76 tabs with the main, "master sheet" having around 2600 entries. The others have anywhere from 50-250. They are alphabetical list of contractors with the areas of work as the differing tabs. My goal is to cross reference the master list of names with the other tabs to see what contractors are registered for what area of work. I am trying to derive a function that look up the text and tell me which tab(s) each one is located. I have been plugging away for some time now with little success. Any help of ideas for formulas or way to organize the data would be greatly appreciate since this is my first major excel project.

**dilipandey** · 05-31-2013, 12:50 PM

HI JMillyard,

welcome to the forum.
You have a master sheet and 76 sheets by the name of contractors.. now

I am trying to derive a function that look up the text and tell me which tab(s) each one is located.

- what text you are referring here.. is it the name of contractor and you need to check if any sheet is there having that text (contractor name) as sheet name ?

Regards,
DILIPandey
<click on below * if this helps>

**JMillyard** · 05-31-2013, 12:53 PM

yes, i need to see what contractor name from the master sheet is in what other tabs, so some may be in 20 tabs, and some may only be in three, i just need to find out which ones they are so i can create another worksheet that shows this as the big picture

**dilipandey** · 05-31-2013, 12:56 PM

but you said that you have sheets having sheet name as contractor names ... so you can use Indirect function in master sheet to reference that contractor's sheet. Do a little research on Indirect function. Thanks.

Regards,
DILIPandey
<click on below * if this helps>