Need Help

[JBJones82]

Need some help I want to have Column A auto fill based on what zone is entered in A1. I am having some problems right now as my formula has become really long the way I been trying and does not work for all Zones. is there an array or another type of formula?


[TABLE="width: 520"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Thanks[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Zone 5[/TD]
[TD]Coax/Aerial[/TD]
[TD]Coax/UG[/TD]
[TD]Fiber/Aerial[/TD]
[TD]Fiber/UG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]v22[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]34[/TD]
[TD]67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]134[/TD]
[TD]1[/TD]
[TD]33[/TD]
[TD]4[/TD]
[TD]65[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]165[/TD]
[TD]3[/TD]
[TD]54[/TD]
[TD]34[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]10[/TD]
[TD]23[/TD]
[TD]76[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Isnt an autofilter better for this job? Put a 'Zone' column in and filter on that.

 

[healey21]

Perhaps some VBA then add a button to run the code

Sub mcrFilterA1()
'Clears all filters
Cells.AutoFilter
'Select cell c3 change this to the first cell
Range("C3").Select
Selection.AutoFilter
ActiveSheet.Range("$C$3:$G$8").AutoFilter Field:=1, Criteria1:=Range("A1")

End Sub

 

[JBJones82]

Isnt an autofilter better for this job? Put a 'Zone' column in and filter on that.

So I thought about doing that but other people have to use the report and they are in separate zones so rather than build 12 different reports for each zone. I wanted to just enter the zone and have it fill based on what zone they enter. Plus some are very Excel Dumb.

 

[steve the fish]

Build 12 different reports? Why would you need to do that with the filter? It cant be that difficult to train people to use the filter button. The data isn't sensitive between them I presume or it wouldn't all be in one workbook. You could try the VBA route but once a macro is run any actions up to that point cant be 'undone'.