| Hyperlinks on Protected Sheets |
|
 |
Index ‹ Excel ‹ Excel Programming
|
- Previous
- 1
- 2
- Excel Misc >> double zerosi have a column where users will enter seconds. however, if the value i
'00' it shows up at '0'. i made a custom format for the column using
'0#' format, so every other entry works. any ideas?
btw, a later column uses this column for calculations so making it tex
format will not solve the problem.
thanks,
jaso
--
Message posted from http://www.ExcelForum.com
- 3
- Excel Misc >> please replyhi i have a proble
i want to format a cell such that it can accept only 13 digits before the decimal and 9 digits after the decima
kindly repl
srikanth
- 4
- worksheet functions >> A calendar calculator template that reflects remaing weeks in a yeHello! :-)
I am trying to create a calendar calc tool that, when I work on a sheet, it:
1) Automatically loads today's date
2) Automatically loads number of pay periods remaining in the plan year
(using a pop down to allow selection of weekly or bi-weekly).
3) Accepts a deduction amount per pay period.
4) Calculates a total annual payroll contribution during the plan year
(based on number of pay periods remaining in the plan year, multiplied by the
deduction).
For Example:
1) Our Plan year: 08./01/05 - 7/31/06
2) Automatically load of today's date: 9/15/05
3) Drop down prompting choice of weekly or bi-weekly pay period: Weekly
3) Remaining pay periods between 9/15/05 and 7/31/05: 46
4) Weekly deduction: $28.00
5) Estimated Maximum Contribution: $1288.00 ($20 * 46 pay periods).
----------------------------
Along the same lines...let's say the goal of the employee to make an annual
maximum of $5000 a year. He wants to know how much he must contribute a pay
period to reach his goal of $5000 within the plan year. Can I create a small
spreadsheet tool that will automatically tell the employee how much he/she
needs to have deducted from his/her paycheck by 7/31/05 in order to reach the
goal of $5000, beginning with payroll of 9/16/05?
This seems like it should be easy, but I cannot get it to work? If you have
an Excel spreadsheet you can send as a template, or if you can describe to
process of creating this, I would be so appreciative!
- 5
- worksheet functions >> Using named range to extend print area for variable number of columnsI've used the following method from D. Peterson to automatically set
the print area to extend to the last row which contains actual data.
Now, I'm faced with a similar situation which the 'columns' are now
the variable component.
Rows to be printed begin at A1:A18 and will always include columns A
and B; there may be as many as 40 columns to include, or as few as the
1st two
These are the 2 components of the named ranges which will set the
number of rows(for reference):>>
Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<>""),ROW(Sheet1!$A$1:$A
$1000))
(Make that 1000 big enough to extend past the last possible row.)
Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)
That last 3 represents the last column to print (A:C in my example).
And change the worksheet (sheet1) if necessary (in all the places).>>
Any ideas? I'd like to keep the named range concept if possible
Thanks for any thoughts.
Pierre
- 6
- Excel Misc >> Need formula Help soonB C E G
2 SubtotalRetail SubtotalCost SubtotalComsn SubtotalGrossProfit
3 =Formula(j7:j10) =Formula(K7:K10) =Formula(l7:l10) =Forml(m7:m10)
A B C D I J
K L M
6 Date PrdoctID QtyOut QtyNow Descpn Retail Cost CmsnGainLoss
7 26.11.07 200700105 2 8 Stonefgr 1300.00 500.00
01600.00
8 26.11.07 200700106 11 0 Stonefgr 3000.00 600.00
26400.00
9 26.11.07 200700068 2 22 Textiles 600.00 320.00
00560.00
1026.11.07 200700249 1 0 Paintings 21000.00 7000.00
14000.00
Sir, just same format of my worksheet. I need a formula in B3, C3, E3 and ...
I want to see what is the exact Gprofit on particular date after data>Filter
of Date Column
I hope I now cleared to you all thing and you will better understand.
Regards
RRS
- 7
- Excel Programming >> Page Protection interfering with Hiding Rowshave a sheet that hides groups of rows based on a cell's information.
I'd prefer this to happen automatically through VBA, but currently I'm
stuck using a Toggle Button to hide/unhide these rows.
The problem I run into though is that that toggle button works
brilliantly until I turn on the page protection. Once I turn on the
protection I keep getting errors that the page is protected and can't
be updated.
And due to the fact that the people who will be using this know
absolutely no Excel at all and break formulas on the old sheet
regularly... well, the sheet has to be write-protected.
The code I'm currently using is this:
Sub DENYPASS()
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("B2") = 1 Then
Rows("11:41").EntireRow.Hidden = True
Rows("42:72").EntireRow.Hidden = False
Else
Rows("11:41").EntireRow.Hidden = False
Rows("42:72").EntireRow.Hidden = True
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
with a private sub to connect it to the Toggle Button
Private Sub passdeny_Click()
DENYPASS
End Sub
- 8
- Excel Misc >> Net profitHi
I have a purchase price in A1 0f 1.12 and a selling price in A2 of 1.95
how do i get the net profit please
RegRDS
mIKE
- 9
- Excel Programming >> Problems with copying and pasting rangeHi,
I have developed the macro below to take a number from a dataset (in
"Sheet1"), copy and paste it into a model (in "Sheet2") and then copy an
paste the model output back into a different column in the original dataset
(in "Sheet1").
Sub Macro()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("Range1")
cell.Resize(1, 1).Copy
With Worksheets("Sheet1")
.Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
cell.Offset(0, 9).Value = .Range("Cell2").Value
End With
Next
End Sub
Here is my issue, rather than simply pasting one value from "Cell2" back
into "Sheet1", I would like to paste a "Range2" back into "Sheet1",
offsetting this value [0,9] from "Range1". However, when I put in a range
rather than a cell-reference in "Cell2", the macro stops working. Does anyone
have any suggestions as to what I am doing wrong?
In advance, thank you very much!
- 10
- 11
- worksheet functions >> degrees formulaon a calculator I would
divide the smaller number by the larger number and then inv tan to get
the degrees
what is the simplest way to get the degrees from a spreadsheet formula,
I have found one way, by getting the two numbers and using radians and
soforth and finally getting my degrees after a couple of steps, there
should be a way to just take the two numbers and formulate degrees
from them
- 12
- Excel Programming >> Clearing a columnHi all,
This question is in two parts.
1) How do you clear an entire column of date in vb code?
2)Is it possible to clear an entire column excluding some cells withi
that colume? For example;
Lets say i want to clear column "A" but want to exclude "A1" and "A2
from this clear function
Is this possible? How!
Cheers al
--
Message posted from http://www.ExcelForum.com
- 13
- Excel Misc >> List folder names within a directoryHello Gurus and newsgroup users.
Your kind assistance please.
For some time now I have been using Chip Pearsons code to
return a list of file names within a specified location. I
would like to now return a list of folder names.
I cannot find any objects releated to folders.
Is it possible?
Any help would be appreciated.
Kind Regards
Paul
- 14
- Excel Programming >> Excel.exeI have a pc running Win NT and office 2000 excel.exe
sortcut will not work. but if I log on as administrator
it work. err is exception access voilation 0xc00000005
address 0x30026E31
- 15
- worksheet functions >> Force "+" signI was wondering if there was a way to create a number format that displays the
plus sign on positive numbers (and the minus sign on negative numbers).
Thanx,
-plh
--
I keep hitting "Esc" -- but I'm still here!
|
| Author |
Message |
DavidHammond
|
 Posted: Tue Apr 05 09:26:43 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
Hi,
I have a protected sheet with some hyperlinks on it. However I am unable to
click on any of the hyperlinks.
The only protection option with anything to do with hyperlinks is the Allow
Insert Hyperlinks option, which has no effect.
My goal is to have a protected sheet with formulas hidden, yet the user can
click on hyperlinks.
BTW the Hyperlinks are inserted as worksheet formulas if that matters...
Thanks
Richard
Excel498
|
| |
|
| |
|
keepITcool
|
| Posted: Tue Apr 05 09:26:43 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
when protecting the sheet:
turn ON :
allow users to select locked cells
allow users to select unlocked cells
then it works for me in xlXP and xl2003
in xl97 i also didnt have any problems.
cell:locked ON /hidden ON formula =hyperlink("http://microsoft.com")
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
Rich wrote :
> Hi,
>
> I have a protected sheet with some hyperlinks on it. However I am
> unable to click on any of the hyperlinks.
>
> The only protection option with anything to do with hyperlinks is the
> Allow Insert Hyperlinks option, which has no effect.
>
> My goal is to have a protected sheet with formulas hidden, yet the
> user can click on hyperlinks.
>
> BTW the Hyperlinks are inserted as worksheet formulas if that
> matters...
>
> Thanks
> Richard
|
| |
|
| |
|
Rich
|
| Posted: Tue Apr 05 19:24:47 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
Thanks for the tip.
Works for me.
I was hoping to be able to hide the cursor, the sheet is a kind of
dashboard, but being able to click links is more important!
"keepITcool" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>
> when protecting the sheet:
> turn ON :
> allow users to select locked cells
> allow users to select unlocked cells
>
> then it works for me in xlXP and xl2003
> in xl97 i also didnt have any problems.
>
> cell:locked ON /hidden ON formula =hyperlink("http://microsoft.com")
>
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Rich wrote :
>
> > Hi,
> >
> > I have a protected sheet with some hyperlinks on it. However I am
> > unable to click on any of the hyperlinks.
> >
> > The only protection option with anything to do with hyperlinks is the
> > Allow Insert Hyperlinks option, which has no effect.
> >
> > My goal is to have a protected sheet with formulas hidden, yet the
> > user can click on hyperlinks.
> >
> > BTW the Hyperlinks are inserted as worksheet formulas if that
> > matters...
> >
> > Thanks
> > Richard
|
| |
|
| |
|
keepITcool
|
| Posted: Tue Apr 05 19:58:42 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
hmm...
Let's get creative!
since a dashboard should fit on 1 screen,
there should be no need to scroll...
and IF you dont want to see that graveyard box...
try following (quick test, seems to work)
in thisworkbook
Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:H24"
Sheet2.Activate
Sheet1.Activate
End Sub
in sheet1 code module
Private Sub Worksheet_Activate()
Worksheet_SelectionChange ([a1])
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(Me.ScrollArea)) Is Nothing Then
Me.Cells(1, 256).Activate
End If
End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
Rich wrote :
> Thanks for the tip.
>
> Works for me.
>
> I was hoping to be able to hide the cursor, the sheet is a kind of
> dashboard, but being able to click links is more important!
>
>
>
> "keepITcool" <EMail@HideDomain.com> wrote in message
> news:EMail@HideDomain.com...
> >
> > when protecting the sheet:
> > turn ON :
> > allow users to select locked cells
> > allow users to select unlocked cells
> >
> > then it works for me in xlXP and xl2003
> > in xl97 i also didnt have any problems.
> >
> > cell:locked ON /hidden ON formula
> > =hyperlink("http://microsoft.com")
> >
> >
> >
> >
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > Rich wrote :
> >
> > > Hi,
> > >
> > > I have a protected sheet with some hyperlinks on it. However I am
> > > unable to click on any of the hyperlinks.
> > >
> > > The only protection option with anything to do with hyperlinks is
> > > the Allow Insert Hyperlinks option, which has no effect.
> > >
> > > My goal is to have a protected sheet with formulas hidden, yet the
> > > user can click on hyperlinks.
> > >
> > > BTW the Hyperlinks are inserted as worksheet formulas if that
> > > matters...
> > >
> > > Thanks
> > > Richard
|
| |
|
| |
|
Rich
|
| Posted: Thu Apr 07 06:39:26 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
Thans for the tip, but I should have made my requirements fully known!!!
The sheet is in a "data file" and I don't want it to have any VBA code, so
people don;t have to get the warning every time if they choose not to trust
me. All code will be in the add-in that will be signed and approved once
only.
I therefore assume that I cannot handle events on that sheet.
Thanks for the tip anyway.
Rich
"keepITcool" <EMail@HideDomain.com> wrote in message
news:EMail@HideDomain.com...
>
>
> hmm...
>
> Let's get creative!
>
> since a dashboard should fit on 1 screen,
> there should be no need to scroll...
> and IF you dont want to see that graveyard box...
> try following (quick test, seems to work)
>
>
> in thisworkbook
> Private Sub Workbook_Open()
> Sheet1.ScrollArea = "A1:H24"
> Sheet2.Activate
> Sheet1.Activate
> End Sub
>
>
> in sheet1 code module
>
> Private Sub Worksheet_Activate()
> Worksheet_SelectionChange ([a1])
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Target, Range(Me.ScrollArea)) Is Nothing Then
> Me.Cells(1, 256).Activate
> End If
> End Sub
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Rich wrote :
>
> > Thanks for the tip.
> >
> > Works for me.
> >
> > I was hoping to be able to hide the cursor, the sheet is a kind of
> > dashboard, but being able to click links is more important!
> >
> >
> >
> > "keepITcool" <EMail@HideDomain.com> wrote in message
> > news:EMail@HideDomain.com...
> > >
> > > when protecting the sheet:
> > > turn ON :
> > > allow users to select locked cells
> > > allow users to select unlocked cells
> > >
> > > then it works for me in xlXP and xl2003
> > > in xl97 i also didnt have any problems.
> > >
> > > cell:locked ON /hidden ON formula
> > > =hyperlink("http://microsoft.com")
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > keepITcool
> > > > www.XLsupport.com | keepITcool chello nl | amsterdam
> > >
> > >
> > > Rich wrote :
> > >
> > > > Hi,
> > > >
> > > > I have a protected sheet with some hyperlinks on it. However I am
> > > > unable to click on any of the hyperlinks.
> > > >
> > > > The only protection option with anything to do with hyperlinks is
> > > > the Allow Insert Hyperlinks option, which has no effect.
> > > >
> > > > My goal is to have a protected sheet with formulas hidden, yet the
> > > > user can click on hyperlinks.
> > > >
> > > > BTW the Hyperlinks are inserted as worksheet formulas if that
> > > > matters...
> > > >
> > > > Thanks
> > > > Richard
|
| |
|
| |
|
keepITcool
|
| Posted: Thu Apr 07 13:21:48 CDT 2005 |
Top |
Excel Programming >> Hyperlinks on Protected Sheets
Rich wrote :
>
> I therefore assume that I cannot handle events on that sheet.
I assumed... famous last words :)
You can set up an application level event handler in the addin..
in thisworkbook of the addin...
Option Explicit
Dim WithEvents appXL As Application
Private Sub appXL_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Debug.Print Target.Address(external:=True)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set appXL = Nothing
End Sub
Private Sub Workbook_Open()
Set appXL = Application
End Sub
now you'll see the address of ANY cell that is changed...
see the dropdowns in the codepane...
select appXL on the left..
check the available events on the right.
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
|
| |
|
| |
|
| |
|
Index ‹ Excel ‹ Excel Programming |
- Next
- 1
- worksheet functions >> If formula true result then divided into 2 columnsI am trying to write an If formula that tells me:
If the value is true then divide the total column by 34% and 66% and put
each percent in a seperate cell.
V13=IF(C13="Both", T12*0.66, T12*0)
U13==IF(C13="Both", T12*0.34, T12*0)
I did this it does not should an error but it basically says that b/c this
formula is making that cell equal 0 so it will calculate 1 column correctly
and the other will have a valueof a zero.
Is there an IF THEN kind of statement...So aggrevating becuase I am so close.
- 2
- 3
- worksheet functions >> Item numbers result in item description in next field in ExcelI am putting a spreadsheet together and I want the item number to
automatically populate the item description in the next field. Can anyone
give me an answer about what syntax I should be using?
For example: In cell B5 I would enter item #123 (I have item #'s in a drop
down) - I would like cell C5 to automatically display the description of 3"
Pipe.
- 4
- worksheet functions >> Adding symbols to text cellsIn column A I have 13,000 rows where there are either values of "JR.", "CPA",
"MD", or nothing. Can I
create an IF statement that says if there is a text value in the cell put
parentheses around that value and if there is nothing in the cell do nothing?
Any help would be appreciated
- 5
- 6
- Excel >> how can i do a vlookup with sum ?how can i do a vlookup with sum ?
e.g.
Given this table
a 1
b 30
b 31
c 2
I want to look for all occurrences of "b" in the first column, and
return the sum of corresponding values in the second column.
So I want the output to be 61
or better, an output of =30+31
TIA
- 7
- Excel Programming >> excel 2003have a question about Excel 2003. I am a programmer but have never used
Excel for
any programming unitl now. (Note the people who setup these spreadsheets,
are long gone).
I have a workbook that has two tabs on it. I know that there is some of a
connection made a sql server 2000 database and there must be a query to
obtain the infomation that updates the excel 2003 spreadsheet. I know this
happens since I
need to hit the 'update option' for each chart on these tabs.
Now the problem is that there is some kind of a problem, and I need to be
able to solve problems.
Ths can you tell me what I need to do in excel 2003 to determine where the
data is coming from and how the data is obtained. There must be some kind of
a query that is written.
I have figured out that if you click on the 'data' on the menu bar, then
import external data, then import data, and then click data sources this will
give you some kind of a default. If does not show where the data is obtained
from and query is used to populate the excel sheet.
Thanks fo your assistance!
- 8
- Excel Programming >> Application.DecimalSeparatorHi all,
I use Application.DecimalSeparator in a function to strip characters from a string and leave just a number.
I noticed that, if you have Tools>Options>International, Use system separators checked, this will not be reflected in
Application.DecimalSeparator.
Can anybody tell me what I should use to get that?
Thanks,
--
Kind regards,
Niek Otten
- 9
- 10
- Excel Programming >> Hiding A RowHello.
I am trying to hide a row based on a value in a cell within the row. I
need this to take place as the sheet re-calculates after a different
selection is made from the Forms combo-box.
Not sure why I'm having issues, as this should be pretty
straightforward.
Any help would be greatly appreciated.
Thanks.
Dan
*** Sent via Developersdex http://www.developersdex.com ***
- 11
- Excel Programming >> Word Or Notepad From ExcelGroup,
I'm writing my first big VBA project. This group has helped me
along quite a bit the past few weeks. I'm comparing two Excel
spreadsheets and checking, comparing verifying fields etc. What should
be my error report listing strategy be? I'm guessing the best way is
Word, since Word uses VBA also. Should I open a word document paste an
error then close it until the next error is found? Or should I leave
the Word doc open and paste to it as errors are found? Or should I use
Notepad and open/close or open and then paste... well you get the idea.
Your thoughts.
Tony
--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17695
View this thread: http://www.excelforum.com/showthread.php?threadid=390078
- 12
- Excel >> Partial Update?Dear all,
There are two range of cells A1:A10 and B1:C10 in my workbook. I want
the content of A1:A10 to be updated automatically when some contents of
B1:C10 is modified. How should I write a macro for it?
I understand that I can just let my workbook update every cell whenever
there is some change. However, as there are thousands of complicated
formulae in the workbook, I would like to suppress the automatic update
function but only update A1:A10 when B1:C10 is updated. Can it be done?
Thanks in advance.
Best Regards,
Andy
- 13
- 14
- 15
- Excel Misc >> I NEED HEEELPPP!!! AUTOFILTERI have this:
A B C
Carl 001 $8.0
Carl 189 $1.5
JOHN 189 $1.5
TOTAL Carl 190 $9.5
JOHN 189 $1.5
I want to have a new sheet where I can insert just the result like this:
JOHN
B 189
c 1.5
Carl
B 190
C 9.5
Now I have 400 differents name, is not just JOHN or Carl, there is a way of
doing it automatically sort by name get a total and insert on the new sheet
and also in one sheet so I select from the sheet the name and it change the
results?
Thank you
|
|
|