Hiding rows/cells in Excel

[piper43]

Hi The I have created a spread sheet using two seperate sheet and hyperlinking them ie customer to information regarding the customer ie contacts etc all in a row now the problem is when I click on the hyper link it gives me the entire page with the row info I need highlighted this is great but I would like to clean it up as in hiding any other rows that are not asscociated to this customer. I have many customers whitht the same link but have diffent rows link to them on the other spread sheet.


I hope you can Help

Jamie

 

[SkipVought]

Jamie,

Don't you have Customer Name on sheet2? Just Autofilter and select Customer Name.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

hey there bascially sheet one has the customers and then yes the second does show the customer ie
sheet one
select as a customer

asd
rty
uio
asd
qwe
then sheet two has information regarding them

ie
asd kjasdfkj askljdhkasjdf lksjdhjkasdh kljsdjkahsdf
rty sdaff sdfdasf sddf sdfsadfsf
uio sadf sdf df dfdf

etc for many customer so how would I filter????


thnaks for the help

 

[SkipVought]

What do you need sheet 1 for? Just Autofilter sheet 2.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

basically the idea is to have one sheet with important customers then click on the customer to view information about them ie sales rep for that account etc there are 9 columns on the second sheet of needed information incdluding the customers on sheet 1 I'm now sure how the auto filter works is it like usisng a pull down tab?? I had started with that thought. So basically it is a lookup for info on each customer.

Jamie

Again thanks

 

[SkipVought]

Data/Filter/Autofilter turns on the Auto Filter. Then you select the dropdown on the column you want and select the value. Pretty simple.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

well skip, that kinda work but still shows additional information about the other customers in the columns\rows below. Also when I set the filter to auto and if there is a space between listing of customer which there is (more so because I created the sheet first using an account assignment sheet account=salesman and went from there adding aditional columns) you have been a great help to someone who is quite new to Excel. I only need the information related to that particular customer shown .

Jamie again thanks

 

[SkipVought]

..still shows additional information about the other customers in the columns\rows below.

I do not know the structure of your spreadsheet, but it does not appear to be the kind of structure that I would use, having OTHER data below my data table or to the right of my data table.

You could HIDE that data that is OUTSIDE OF YOUR TABLE -- select the ROWS/COLUMN , right click & select Hide.

Also when I set the filter to auto and if there is a space between listing of customer which there is ...

This appears to be a TABLE that is NOT A TABLE -- in other words a table with EMPTY ROWS or COLUMNS. This is not good table design. ALL the data in your table needs to be CONTIGUOUS.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

Hey Skip your right thanks I have taken out the spaces etc and will try ands see if it meets our needs I just basically want our sales support to use this when looking up who belongs to what and any special in. Thanks for all of your help.

Jamie

 

[SkipVought]

If you really needed to run this from another sheet, It can be accomplished with several LOOKUP functions.

If A1 Contains a Valid Name and the structure on sheet2 is
Name, Address, Phone, with headings in row 1, THEN
[tt]
B1: =INDEX(Sheet2!$A$2:$C$999,MATCH(A1,Sheet2!$A$2:$A$999,0),2)
C1: =INDEX(Sheet2!$A$2:$C$999,MATCH(A1,Sheet2!$A$2:$A$999,0),3)
[/tt]
In addition, you could set Data/Validation on A1 - Allow: List - Source: =Names (CAVEAT: The range Sheet2!$A$2:$A$999 must have a Range Name, Names)
Then you can select from the list and the data for that customer will be displayed adgacent.


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

hmmm sounds like that might be it but will I have to remove all the hyper links??? ya another silly question lol
and I can just add that a s a formula correct??

 

[SkipVought]

These are formulas,not hyperlinks.

Just create another sheet.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

Hi Skip thanks again for your help I'm thinking the auto filter might due no the question is can I do the auto filter on just the one column

Account BDM ISO SA CSM Products Margin

As the I have more heading than this but the auto filter shows on each column it would be really nice just to have it on one column ie Account

thanks again Jamie

 

[SkipVought]

In reality it does not matter. But you can put an AutoFilter on only one column.
[tt]
Select the column.

Turn on the AutoFilter.
[/tt]


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

I did that skip and yet it place the pull down tab on all columns??

Jamie

Is this normal the other column do no show all the info like the account column

 

[SkipVought]

What version of Excel are you using? Please explain EXACTLY what you did.

Is this normal the other column do no show all the info like the account column

I do not understand your question.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

Hey Skip,
I'm running MS Office 2003 Excel
11.5612.5606
what I did was select coulumn Accounte and then Data and then Auto Filter. this worked but also pu the auto filter on all coulmns as well ie BDM I createda seperate sheet with three column of garbage now the account column is on A but at row 8 and I did select that cell to do the auto filter I do have a title etc



Customer Special Pricing

Please Choose an Account


Account BDM ISO SA CSM Products Margin

So it place the auto filter on all coulumn not on the one that I selected ie Account every coulmn had it's own pull down tab beside it.

Jamie

 

[SkipVought]

If you have this stuff ABOVE your table, you can't do what I suggested, ie select column A. This ASSUMES that the HEADING ROW is in ROW 1

Instead,
[tt]
select the cell containing heading, Account

Holding [shift] hit [End] then [Down Arrow] - this will select the Account Data

Turn on the Auto Filter
[/tt]
Once you figure out how to do this, I'd macro record the process and associate with a keyboard shortcut.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[piper43]

Awsome It worked!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

thanks for all your help Skip

I'm sure I will have plenty more stupid questions

Jamie

Thanks