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

#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.


Deiota wrote:
>
> 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

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

Deiota wrote:
>
> 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

In message 72D9CB06-54DA-478E-9CFE-(E-Mail Removed),
Deiota <(E-Mail Removed)> Proclaimed from the tallest tower:


>
> - 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...

--
Regards,
Chris.
(Remove Elvis's shoes to email me)

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" wrote:

> #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

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


Deiota wrote:
>
> 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" wrote:
>
> > #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

--

Dave Peterson

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
"string" wroten 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" wrote:

> 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
>
>
> Deiota wrote:
> >
> > 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" wrote:
> >
> > > #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
>
> --
>
> 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.

Deiota wrote:
>
> 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
> "string" wroten 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" wrote:
>
> > 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
> >
> >
> > Deiota wrote:
> > >
> > > 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" wrote:
> > >
> > > > #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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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" wrote:

> 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.
>
> Deiota wrote:
> >
> > 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
> > "string" wroten 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" wrote:
> >
> > > 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
> > >
> > >
> > > Deiota wrote:
> > > >
> > > > 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" wrote:
> > > >
> > > > > #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
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> 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




Deiota wrote:
>
> 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
>
>