Formula / Multiple Sheet Reference Assistance

Laura Senff

Hello!

I have a summary sheet that references five sheets. I already had to modify my ranges for VLOOKUP on those sheets because I hit that 25,000 cell reference within moments. Three weeks later after changing all of my sheets, I'm ready to proceed to the next step in my project.

I have one column that uses a VLOOKUP to reference a range on one sheet and pull in a crew assignment based on a unique work order #. I have three contractors with individual sheets. I would like to use the previously mentioned contractor assignment to search out the unique work order number on the individual contractor sheets to pull in project duration, start date, finish date, and % complete information.

 

My understanding of the formula is something similar to this (but obviously the below is not correct or I wouldn't be asking for help): 

=IF([Crew] = "Contractor 1", =(VLOOKUP([WO#], {Contractor 1 Range}, 3, false)) =IF([Crew] = "Contractor 2", =(VLOOKUP([WO#], {Contractor 2 Range}, 3, false)) =IF([Crew] = "Contractor 3", =(VLOOKUP([WO#], {Contractor 3 Range}, 3, false))

Any assistance would be greatly appreciated!

 

 

Shaine Greenwood

Hi Laura,

Looks like you've got an extraneous equal sign and some parens out of place. It also appears that you don't have a specific cell reference for your search criteria and your Crew.

Try something more like this:

=IF([Crew]1 = "Contractor 1", VLOOKUP([WO#]1, {Contractor 1 Range}, 3, false)) =IF([Crew]1 = "Contractor 2", VLOOKUP([WO#]1, {Contractor 2 Range}, 3, false)) =IF([Crew]1 = "Contractor 3", VLOOKUP([WO#]1, {Contractor 3 Range}, 3, false))

Replace the 1s with the specific cells in your sheet.