Excel formula

[Sparx]

Hi there,

 

I'll try to explain this as best i can. Basically i use a massive spreadsheet alot at work with lots of information on back sheets being pulled through to front sheets for display to directors. Basically I'm looking for a formula which will lookup and match 2 values in a table and then display the reems of information below.

 

For example,

 

On sheet 1 i want to be able to use 2 drop down menu's to select a site name and house type and then due to matching these 2 figure to the master back sheet, display all of the information with regards to trades below in specified lines. I'm sure its possible but I just need a little guidance how.

 

If its unclear as to what i need please say and i will try and word it different, maybe even with pictures if need be.

 

Thanks in advance for any help given.

 

Sparx

[Guest Wolfeymole]

Well I'm a little rusty on Excel these days Sparx but to my mind you'd be better running this as a Query in Access.

[Sparx]

I agree that access would be better but i'm kinda editing an excel sheet which someone else has created and that they keep updated so i kinda need to find a way of doing it in excel.

 

I've just quickly knocked up a fake table to help me explain:

 

Master sheet

 

Value 1 H1258

Value 2 P409-

 

 

Value 3 20000

Value 4 154

Value 5 18118

Value 6 12118

Value 7 11158

Value 8 3336

Value 9 8895

Value 10 5987

Value 11 6568

 

So basically I want to be able to enter H1258 in a cell on another sheet, which will then check all the "Value 2" figures to match another figure that i type in on the front sheet, this will then return values 3-11 in cells below. Basically it has to match the Value 1 figure with the Value 2 figure and then return the other values.

 

Hope that help explains better. I know its a tricky one, i've been fiddling with lookup and match formulae for ages trying to work this out. :op

 

Sparx

[Guest Wolfeymole]

Well I'll have a play around Sparx but don't hold your breath.

[Sparx]

Thanks mate, i'm pretty sure and index & match combination can do it. Just not sure how.

[Sparx]

I've just knocked up an example spreadsheet, basically i want it to be able to enter the Site & House type in the blue squares and it to fill in the corresponding information in the purple squares.

 

Thought doing this would help make it easier for people to play with if they decide to try and help.

 

Thanks

 

Sparx

Sample spreadsheet.xls