Referencing dependencies....with Vlookup?

**3smees23** · 05-17-2012, 07:29 AM

Hi,

I'm trying to display equipment that is dependant on other pieces of equipment being present on a spreadsheet.

I’m working in the green box on the attached sheet ('take off' tab).

I want cell L46 to contain a formula that checks table E45:K72 and cross references the 'dependencies' tab. If it finds any part numbers in column A ('dependencies' tab) in table E45:K72, then display the dependant part number (column B in 'dependencies tab') in L46 ('take off').

For example, L46 could contain 'Wieland DBC905W Supply Connector Pack' as that is the dependant part of the 'DBC905W' in row 67.

Can N46 also display the quantity stated in the E45:K72 table? I had a go at the formula in N46 but I think it's way off.

Thanks for your continued support :-)

Sam

**bonny24tycoon** · 05-17-2012, 08:07 AM

Hi There,

Not sure, if this is what you need. Blank New Take Off Sheet_amended.xlsm

Thanks,

Bonny Tycoon

**If I was able to help please click the small star icon at the bottom left of my post **

**3smees23** · 05-17-2012, 08:15 AM

Hiya,

Thanks...It's kind of what I want. But rather than changing how the spreadsheet looks, can you use the size of the original green box?

I basically just wanted a list of any additional items that I may forget to include in the take off. It will never exceed the size of the green box as my dependencies are limited.

Is this possible?

Many Thanks

Sam

**bonny24tycoon** · 05-17-2012, 09:47 AM

Hi,

Would a macro automation help?

Thanks,

Bonny Tycoon

**If I was able to help please click the small star icon at the bottom left of my post **

**3smees23** · 05-17-2012, 10:14 AM

Hey,

If that's the best way. Would you be able to assist?

Cheers

Sam

**bonny24tycoon** · 05-17-2012, 10:35 AM

Hi,

Im trying my best

Have completed 50% yet.. I think the macros needs some tweaks here and there.. Will keep you posted if im successfull.

I have just received some urgent work from my boss

Its a big task and needs urgent attn,.. Would be working on your query as an when I get time. Apologies

Thanks,

Bonny Tycoon

**If I was able to help please click the small star icon at the bottom left of my post **

**3smees23** · 05-17-2012, 12:03 PM

That's absolutely fine

. Thanks for all your help.

Hope you don't get too stressed out with your boss's work

Thanks

Sam

**NBVC** · 05-18-2012, 08:17 AM

Try this formula approach....

In the Dependancies tab, enter formula in D2:

=IF(ISNUMBER(MATCH(A2,'Take-Off'!$E$46:$E$72,0)),COUNT(D$1:D1)+1,"")

copied down

In Takeoff sheet L46:

=IFERROR(INDEX(Dependancies!B:B,MATCH(ROWS($L$46:$L46),Dependancies!$D:$D,0)),"")

copied down and, if desired, to next column.

In N46, enter:

=IFERROR(VLOOKUP(INDEX(Dependancies!A:A,MATCH(L46,Dependancies!B:B,0)),'Take-Off'!$E$45:$K$72,3,FALSE),"")

copied down.

I am not sure what you want in O46, but maybe you can figure it out from formulas given above....

**bonny24tycoon** · 05-18-2012, 11:17 AM

Hi Sam,

Im back.. Let me know NBVC has solved your issue

Thanks,

Bonny Tycoon

**If I was able to help please click the small star icon at the bottom left of my post **

**3smees23** · 05-18-2012, 01:45 PM

Hi Gents,

NBVC's formula works really well!! But I have noticed a problem with it, if you look at J14 (takeoff), the DTP170-WB has been selected but hasn’t got a qty in....then look in the green box N50 and it says I need 3x DDNP1501's. It's as if it’s copying it from N51 as I have 3x DTP100's in L14. The DTP100 and DTP170-WB both need DDNP1501 PSU's.

Another couple of points that will make this even better? Can the green box consolidate any lines of DDNP1501 (as this dependency is needed for two pieces of equipment as stated above). eg if I had 1x DTP170-WB and 1x DTP100 it would say I need 2x DDNP1501 in one line only?

Lastly, If I have selected the dependant equipment i.e. 2x DDNP1501, (and its already in my E45:K63 table) can the dependency formula be void?

I have highlighted the cells I’m referring to above in yellow to make it a bit easier to see what im talking about.

Thanks so much for your help guys. You're both legends :-)

Cheers

Sam

**NBVC** · 05-18-2012, 02:31 PM

For the first part, in the Dependancies tab, change formula in D2 to:

=IF(AND(COUNTIF(B$2:B2,B2)=1,ISNUMBER(MATCH(A2,'Take-Off'!$E$46:$E$72,0))),COUNT(D$1:D1)+1,"")

copied down and add formula in E2:

=SUMIF('Take-Off'!$E$46:$E$63,A2,'Take-Off'!$G$46:$G$63)

copied down

Then back in the Take-off tab

change N46 to: =IF(L46="","",SUMIF(Dependancies!B:B,L46,Dependancies!E:E)) copied down

I am not sure what you mean with:

Lastly, If I have selected the dependant equipment i.e. 2x DDNP1501, (and its already in my E45:K63 table) can the dependency formula be void?

**3smees23** · 05-18-2012, 03:07 PM

Hi NBVC,

Ok...so I have selected 3x DTP100's (total in L14), but this time I have remebered to add 3x DDNP1501's (total in O14) that i need with the DTP100's.

As I have remembered to include these three dependent parts, can the reminder in the green box (L48:O48) not be there.

Hope this makes more sense :-)

Many thanks

Sam

**NBVC** · 05-18-2012, 03:20 PM

So is there a chance you will select 2x DDNP1501's (total in O14) instead of 3? And if so, what should happen in the green box?

**3smees23** · 05-18-2012, 03:27 PM

potentially yes, then the green should prompt me to add 1x DDNP1501

Cheers

**NBVC** · 05-18-2012, 03:42 PM

Try these amendments only in the Dependencies tab...

in D2:

Please Login or Register  to view this content.

in E2:

Please Login or Register  to view this content.

**3smees23** · 05-18-2012, 03:54 PM

Wow wow wow NBVC. This is great. Thanks for ALL your help.

There are a couple of other things I cant work out so I will post on here a few more times.

Kind regards

Sam

Referencing dependencies....with Vlookup?

LinkBack

Thread Tools

Rate This Thread

Display

Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Re: Referencing dependencies....with Vlookup?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions