HELP!!!!!!!

[Guest]

Hello!
I'm doing a sheet at work and I'm having some problems, I need your help!
Whay I have is a sheet with some columns and rows.

- 1: Can I put "filters" on SOME columns only? I can put filter with
auto-filter to all columns, but it makes no sense... How to put on SOME only?

- 2: Can I auto create a book when inserting some data on a cell (write down
something on a blank cell)? use the same name as text written on cell?

- 3: Complete some cells auto from specific cells on other sheet (file) of
excel?

- 4: Hide some columns so some users on a Domain don't see... IMPORTANT...
(Like prices of products)

Thanks Deiota

 

[Dave Peterson]

#1. You can apply Data|Filter|Autofilter to any contiguous range on the
worksheet--you don't need to use all 256 columns (or 16k columns).

The only way to hide those arrows is via code. Debra Dalgleish shows how:
http://contextures.com/xlautofilter03.html#Hide

#2. Are you asking how to save a workbook with a name that is in a cell in one
of its worksheets?

You can with a macro:

Dim myCell as range
set mycell = thisworkbook.worksheets("sheetnamehere").range("a1")
Thisworkbook.saveas filename:=mycell.text, fileformat:=xlworkbooknormal

This does no checking at all--whether there's something in that cell--or whether
it could be used as a name in Windows (or whatever your OS is).

#3. You can use formulas to retrieve a value from a different worksheet in the
same workbook:

='other sheetname here'!a1
or
=if('other sheetname here'!a1="","",'other sheetname here'!a1)

You can create the same kind of formula between workbooks by typing it in, but I
like to let excel do the work.

Open both workbooks.
select the "sending" cell
edit|copy
go to the other worksheet in the other workbook.
select the "receiving" cell
edit|paste special|paste link

You may want to adjust the formula to hide the 0 when that sending cell is
empty.

#4. Anything you put in a worksheet can be made visible to anyone who can open
the workbook--excel's security isn't made for this type of intellectual property
protection. It's made to protect the casual user from overwriting cells that
shouldn't be touched.

If you don't want the prices getting out, don't put them in excel. If you have
to put them in excel, don't share that workbook with anyone.

 

[Dave Peterson]

ps. For anything to do with macros...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[ChrisM]

In message (e-mail address removed),

- 4: Hide some columns so some users on a Domain don't see...
IMPORTANT... (Like prices of products)

Thanks Deiota

You could put the prices in a seperate spreadsheet file, and use Windows
Security to give access to this file only to users that are allowed to see
the prices.
Then in the general spreadsheet, create an 'external datarange' link to the
prices in the restricted sheet.
Not sure exactly what Excel does when a linked sheet is unavailable, so you
might have to do some fancy formula to hide any errors that might come up if
it in inaccessable, but you should be able to do it just with
formulae(ONERROR(??)) no need for any macros...

 

[Guest]

Hello!
Thanks for your reply!
I have a sheet with 9 columns and I want to show filter on column 2, 4 and
7. How do I do that?
I miss the VBA language and I'm trying pretty hard to construct a macro but
I can't.
Please help!
Deiota

 

[Dave Peterson]

I'm not sure what range you're going to filter, so you do that manually.

But after you have filtered your range, you can run this macro (based on that
code from Debra Dalgleish's site):

Option Explicit
Sub HideArrows()

Dim myCell As Range
Dim iCtr As Long
Dim WhichColumn As Long

With ActiveSheet
For Each myCell In .AutoFilter.Range.Rows(1).Cells
WhichColumn = myCell.Column - .AutoFilter.Range.Column + 1
Select Case WhichColumn
Case Is = 2, 4, 7
'do nothing
Case Else
myCell.AutoFilter Field:=WhichColumn, _
Visibledropdown:=False
End Select
Next myCell
End With

End Sub

Make sure that the correct sheet is active when you run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.
Hit alt-f8 to see the list of macros.
Select this one (HideArrows) and hit run.

If you never have to use this again, you can delete that code.

If you don't delete it correctly, you may be prompted each time you open the
workbook.

Check out Debra Dalgleish's notes:
http://contextures.com/xlfaqMac.html#NoMacros

 

[Guest]

Just made it some other way, also working:

Sub HideArrows()
'hides all arrows except column 2, 5, 6, 7, 8, 11, 12
Dim c As Range
Dim i As Integer
i = Cells(1, 1).End(xlToRight).Column
Application.ScreenUpdating = False

For Each c In Range(Cells(1, 1), Cells(1, i))
If c.Column <> 2 And c.Column <> 5 And c.Column <> 6 And c.Column <> 7 And
c.Column <> 8 And c.Column <> 11 And c.Column <> 12 Then
c.AutoFilter Field:=c.Column, _
Visibledropdown:=False
End If
Next

Application.ScreenUpdating = True
End Sub

Perhaps stupid... structured

Another thing, I can't make it work #2, save a a new sheet (with name as
n on column A .

Like, table with A column name. I want to create a sheet as long as I imput
a name on it, like John, Frances etc... To have a sheet for each student!
Please help!

 

[Dave Peterson]

Say your list of names is in A1:Axx (no gaps).

Option Explicit
Sub testme()

dim iRow as long
dim FirstRow as long
dim LastRow as long

with worksheets("Nameofsheetwithlisthere")
firstrow = 1
lastrow = .cells(.rows.count,"A").end(xlup).row

for irow = lastrow to firstrow step -1
worksheets.add.name = .cells(irow,"A").value
next irow
end with
end Sub

=======
This doesn't do any validation at all. Don't have any illegal names and don't
have any duplicates.

 

[Guest]

I will try this out as soon as I get to work.
can I mke a template sheet to open as the name I am entering?

the thing is: i'm creating a table of repairs for my company's with number
of file, client name, equipment to be repaired, dates and costs and all..
well, the main sheet is this table, and for each repair I want to create
another sheet (auto) with specific details. what I mean is I've created a
template with fields needed on that detailed sheet, like components needed
and quantity and costs, and simple formulas to maye it easy.
wheel, when I say I want to create another seet with name writen on cokumn A
IS this sheet (template). is it possible???

THANKS
deiota

 

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets("Nameofsheetwithlisthere")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
Worksheets("Template").Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = .Cells(iRow, "A").Value
Next iRow
End With
End Sub

 

[Guest]

Hello!

I'm having various problems with this work, but for now, I want to solve
just two of them!

The thing is:

I have a book with 3 sheets : general, clients, products.
on the general sheet, I have a table with some repairing stuff.

1º I want to row "clients", say Column B, just to access to database on
clientes sheet (on column sheet with clients name).
The thing is I dont want to appear written JFC and J. F. C.. Just choose
something on the database (Clients sheet).

2º I have a sheet (products) that have 2 columns : reference of the product
and description.
I want to, at first, just be able to choose a ref pre existing on "products
sheet, ref column, say A column).
Then, automatically, when I choose ref. XXXX on column A it completes the
next Column on General sheet, with description matching the XXXX product on
Products sheet.
Of course, I want the reverse, to be able to choose a description (from the
ones on Products B column) and associate it on Ref column on General.


Do you know what I mean????

Please help me!!

I can send you the files and you can understand bettter.. Add me to MSN, if
you wish

 

[Dave Peterson]

#1. Maybe you could use Data|Validation to get a dropdown so you could choose
the name you want.

See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

#2. You could use data|validation to choose one item and then use =vlookup() to
get the description.

With the item in A1 and the description going in B1, you can put this in B1:
=if(a1="","",vlookup(a1,products!a:b,2,false))

Again, you could see Debra Dalgleish's site for help with =vlookup().
http://contextures.com/xlFunctions02.html

 

[Guest]

hello again!
I want to thank all the help you've gave me, it's helping me a lot!
I have a few more questions to ask:

1- one column is to input a number like this 001/01 (number/month), but
there are no limit for number, I dont know if will be 001/01...045/01 046/02
047/02....267/12...
I only know that is a sequential number (integer) / month (and the sequence
passes to the other month, like the example above!!

2- I have a colum that is "month/year-###", like 06/05-179. How can I format
this column?

3- The same "month/year above", I want to grab this data and subtract to
month/year of "today", to see if the product is in warranty or not!. how can
I do it= I mean? grab the data from part of the data on item 2 and the
subtract to today's month and year?

4- Can I hide a sheet and unhide it with a password? and a Column on a sheet?

For now, it's all about that!
Thanks a lot!
Deiota