Dependant cells

[Aussiebear]

Two young enterprising lads (Zack & Jake) bought the local parts and service centre at Walkabout Creek and promptly named the business Walkabout Creek Fixerup Services. However they were having a bit of a problem with what parts were in the shop, so decided to hold a 5 day, in-house team building camp out at the local watering hole. About 3 and a bit days into the bender ( sorry I mean team moral building exercise where they were discussing the need for a spreadsheet), when Zack's missus sent a message to the Publican saying that could he send the useless sod home to fix the damage caused when Rutter the feral boar and Zack's bull Mastiff cross "Fluffy" had a bit of a disagreement about who had rights to the scrap bin. Apparently most of the carrots, beans and only remaining cabbage were scattered about the yard. Zack, you remember the cabbages you were meant to spray for moths last week, but you told the wife that you had run out of the good stuff but there was a small drum of Agent Orange in the back of the shed, that you thought would give the moths a run for their money. The Missus knew something was up when you took off with the big esky and two fishing rods.....

Anyway the accountant Paul, sent our boys a spread sheet with Part Numbers and a Part description list and the boys were instructed to fill in the Alley Colour and Bin Numbers for the parts. On sheet 2 of the workbook is the current parts in stock. Some of the more astute of you will notice that the location of parts is... well haphazard to say the best, but as all young enterprising lads are often heard to say " we're a little busy right now but we plan on resorting the shed at the next available opportunity. Perhaps in the wet season. They've heard that George a local cocky is soon going to be putting in an order for spare parts and the boys need to both find the parts and be able to correctly invoice George.

On sheet 1 of the Accountant's workbook is a small 4 cell range with dropdowns relevant to the header for each dropdown, The experienced accountant Paul wants the ability to simply enter in the Part Number and the other three cells are automatically filled in with the correct data, where as Jake would rather simply enter in the Part Description and have the other cell completed, and Zack ... well he's got a rough idea where the part could be and is willing to go along with Jake's Idea.

Can anyone write some code to enable either method to be used please?

[georgiboy]

Nice story, you could have a cell for 'Part #' and a cell for 'Description' and then next to that the 4 cells you had and use XLOOKUP if using Excel 365.

Are you using Excel 365 for this?

[Aussiebear]

Yes 365. I have four cells already in place each with a dropdown if that helps you, and George, go easy with the formula stuff. You have a history of writing long long formulas once you get started,

[Paul_Hossler]

Two young enterprising lads (Zack & Jake) bought the local parts and service centre at Walkabout Creek and promptly named the business Walkabout Creek Fixerup Services. However they were having a bit of a problem with what parts were in the shop, so decided to hold a 5 day, in-house team building camp out at the local watering hole. About 3 and a bit days into the bender ( sorry I mean team moral building exercise where they were discussing the need for a spreadsheet), when Zack's missus sent a message to the Publican saying that could he send the useless sod home to fix the damage caused when Rutter the feral boar and Zack's bull Mastiff cross "Fluffy" had a bit of a disagreement about who had rights to the scrap bin. Apparently most of the carrots, beans and only remaining cabbage were scattered about the yard. Zack you remember the cabbages you were meant to spray for moths last week, but you told the wife that you had run out of the good stuff but there was a small drum of Agent Orange in the back of the shed that you thought would give the moths a run for their money. The Missus knew something was up when you took off with the big esky and two fishing rods.....

Anyway the accountant Paul, sent our boys a spread sheet with Part Numbers and a Part description list and the boys were instructed to fill in the Alley Colour and Bin Numbers for the parts. On sheet 2 of the workbook is the current parts in stock. Some of the more astute of you will notice that the location of parts is... well haphazard to say the best, but as all young enterprising lads are often heard to say " we're a little busy right now but we plan on resorting the shed at the next available opportunity. Perhaps in the wet season. They've heard that George a local cocky is soon going to be putting in an order for spare parts and the boys need to both find the parts and be able to correctly invoice George.


This sounds like a homework assignment :)

[georgiboy]

:rotlaugh:

[Aussiebear]

Edumacation is top stuff round 'ere in Walkabout Creek.

[Aussiebear]

I have followed your advice George and changed the cells to run vertically with only Part # (D4) and Part Description (D5) now listed as DV cells. In cell D6 Ihave the formula =Xlookup(D4,PartNum,AlleyColor,0) to return the Colour of the Alley in which to look for the bin. In Cell D7 I have a similar formula =Xloookup(D4,PartNum,BinNum,0) which returns the correct Bin Number for the Part required.

However I am more interested in the concept of using either cell D4 or D5 to drive the selection process.

[georgiboy]

This is what I had in mind, just added a little code to clear one cell if the other cells dropdown is used.

[georgiboy]

Here's another option where you don't have to change the layout that you had.

[Aussiebear]

George, can you explain the line


If Target.Address(False,False) = "C8" Then

Also in the alternative workbook you use


Var=EVALUATE("XLOOKUP(" & Target.Address &",'DataSheet!A:A,'DataSheet!A:D,"""",0")

There are seven Double Apostrophies, is one a left over or am I missing something here.

[georgiboy]

I think i updated the spreadsheet a little while after i uploaded it so the first line you mention was replaced BUT'
Writing this:

If Target.Address(False, False) = "B8" Then
Is the same as writing:

If Target.Address = "$B$8" Then
The (False, False) part removes tha absolute range so "$B$8" would become "B8"

On this line:

var = Evaluate("XLOOKUP(" & Target.Address & ",'Data Sheet'!A:A,'Data Sheet'!A:D,"""",0)")
I count 8 "

[Aussiebear]

its seems I can't count.... Sorry George.