Excel Query

[Lee24]

Hi All......

Hopefully someone can help with my problem......

I want to be able to have several entries against a particular code, so that when i search under a code it lists all the materials under it.

Any ideas???

Thanks

L


Refer below

1.1 Materials Cost

A
B
C
D
E
F
G
H
I
J

Total £0.00


2.1 Food

K £0.00
L £0.00
M £0.00
N £0.00
O £0.00
P £0.00

£0.00

 

[xlhelp]

Monsieur, a little more explanation, sil vous plait.

Member- AAAA Association Against Acronym Abusers

 

[Lee24]

Hi,

What I am trying to do is have a row of lines tied into 1 code

So for instance if I wanted to search for code 1 it would display everthing link to code 1, the only other way i could do it is is give each line the same code ie 1 beside Twix and Crisps but I dont want to have repeating data in the code column

Example

Code Description

1 Mars Bar
Twix
Crisps

2 Steak
Fish
Chicken

 

[SkipVought]

Hi,

Use the PivotTable zisard to produce that report.

OR

list the CODE on each row and use Format/Conditional Formatting to make it APPEAR to be empty by shading the font the same as the cell interior. (standard operating procedure in many shops)

Skip,

_[red][/red]

 

[Lee24]

tried that just get errors.....

can i email the file for you to view if possible?

 

[SkipVought]

ii36250 at bellhelicopter dot textron dot com

Skip,

_[red][/red]

 

[Lee24]

Tried that.... can't get it to work...

 

[Lee24]

leecurrie258 at hot mail dot com

 

[SkipVought]

Lee,

You explicitly shaded the repeated cells' font WHITE. I changed that on the NAME column, using ConditionalFormatting, a much better solution since it’s a GENERAL solution.

Your data would be much easier to manipulate, if it were CONTIGUOUS. It would make applying the AutoFilter very simple. Using your disjointed format, you must select ALL the related data with heading(s) in order to apply the filter properly.

FYI, the word, query has a specific meaning. A query is not a question. It is a data acquisition procedure that can be employed in many MS application to return external data to the application.

Skip,

_[red][/red]

 

[Lee24]

Is their no way I can have a single box at the top (data validation) and allow the user to pick from this?

Thanks

L

 

[SkipVought]

Of course. Did you see the AutoFilter drop down on the Name heading?

Skip,

_[red][/red]

 

[Lee24]

Yeah I did, is there any way that it can contain just specific options ie names of those listed on the sheet and one for everything and lose the top 10, custom, etc etc options?

 

[SkipVought]

Now you're talking some fancy programming - VBA. Yes, you could have a Data/Validation box using a LIST of the NAMES on your sheet. The selection of a NAME in the Validation box triggers the Worksheet_Change event in which you would have logic to determine if the change were in the Validation cell and then take that value and apply it to the AutoFilter criteria.

If you want to go that route, please repost this question in VBA Visual Basic for Applications (Microsoft) Forum707.

Skip,

_[red][/red]

 

[SkipVought]

Additional thoughts on your sheet.

Do not subtotal in your table. rather use the SUBTOTAL function on the entire range of data to SUM (9). I prefer aggregations at the TOP for several reasons. 1) its there for the user to see immediately 2) its ALWAYS in the same place 3) its a simple approch

Remove all empty row and comumns. Use row heights and column widths to display with the white space that you want.

Skip,

_[red][/red]