Free Microsoft Excel 2013 Quick Reference

hidden links

I'm trying to find where external links are hidden in a large spreadsheet (about 20 worksheets total)...it seems like the links are burried somehow in a specific worksheet name?, not in cell values as formulas or in a name ("insert, name, define" like I'm used to seeing. I see the external link listed under "edit, links" and I can narrow down to the worksheet that contains the link, but when I delete the entire content (cell values, even the empty ones) of the worksheet, the link still shows, but when I delete that worksheet the external link disappears...does anyone know how I can find and delete the external links without deleting the worksheets they are in?


Post your answer or comment

comments powered by Disqus
I have a pretty large workbook that has had bits and pieces pulled from different things. However, it seems to have links which I can not remove and am kind of stuck. There is only one single link I want and that is a button that runs a macro on another workbook and that is the only one that shows.

Anyone know how to remove these "hidden" links? They do not exist in any formulas. I have a feeling that it is increasing my workbook size also.

In 2003 I used to use the Findlinks marco to find hidden/ghost links, but I dont think it works in the 2007 version. Is there a way to find the hidden links in Excel 2007?

Hi

when i import a webpage using data import, i have no problem to show the text from the webpages.

However is that possible for me to pull the link in the cell which always start with "javascript:click('http....")

thank you

I want to link a few sites to some cells in excel. I would like place a
link within specific text.
Ex. _Link_name ("http://www.google.com")_ and have
the URL hidden. Link example goes to Google.com.
Thank you,
Joe

--
JoeStL
------------------------------------------------------------------------
JoeStL's Profile: http://www.excelforum.com/member.php...o&userid=29080
View this thread: http://www.excelforum.com/showthread...hreadid=491317

I have a file that was created by using Save As from a previous version. When I open the new file, I get a security warning asking if I want to allow external content. If I click Allow, then Edit Links, I get a window showing a link to the original version of the file (which no longer exists). If I highlight that filename and click Break Links, I hear a ding, but the link is still there.

Because of corporate security settings, I cannot set the file location as a trusted source, so I am faced with this warning every time I open the file.

I have tried to find the hidden link in the file by making a copy and deleting things one by one until I no longer get the External Data notice. I narrowed it down to one worksheet (if I delete that worksheet, the notice goes away), then tried deleting the data in that worksheet one column at a time. When I deleted all the data, I still got the notice, so I figured the link was hidden somewhere in the unused portion of the worksheet. I created a new sheet, then copied the data I need, and deleted the old sheet. However, I am still getting the Security Warning, and still cannot break the link.

Any ideas?

I have several worksheets linked to other worksheets in an Excel workbook. I
want to e-mail the workbook to several different groups with each group only
being able to view specific worksheets. Is it possible to hide and then
protect the worksheets so that there are different passwords each group uses
to only access the sheets we want them to see?
--
News Gal

I want to link a few sites to some cells in excel. I would like place a link within specific text. Ex. Link name and have the URL hidden. Link example goes to Google.com.Thank you,
Joe

Hi. I have learned how to populate data in a dependent workbook/sheet from data in a source workbook/sheet. However, i'm still having problems. The dependent workbook has the same basic formatting and information as the source, but without many of the source's columns, and with smaller row height and column width.

I'm using this to track the dates when certain information passes through a system; e.g, Doc Prepared Date, Doc Issued Date, Doc Read Date, Doc Accepted Date. These would be the columns. The dependent workbook is similar, but without, say, the Doc Read Date column. The source workbok has different formatting (smaller row sizes and column sizes are the main differences).

I'm using 2 rows for each Document. Column A has account numbers. I have merged Cells A1 & A2. This information may or may not go to cells in the destination workbook that have the same reference (A1/A2). Several more columns have the cells from the 2 rows merged. Then, there is a section where i do not merge the rows...they remain separate. In the upper cell (this would be Row 1), i put a due date. In the lower cell (Row 2), i put the received date. I have several columns like this (with Row 1 & Row 2 cells unmerged). Then, later on, cells are merged again; e.g., Z1 & Z2 are merged.

I have been able to link information from the source to the destination workbook, but not every time. if i type something in, like the number 42, it works just fine. It also works if i take the source information in merged cells A1/A2 and have populated in the destination workbook merged cells A1/A2. However, whenever i try to have the source information in A1/A2 populated to D1/D2 or A3/A4 (all also merged cells). I get #VALUE! in the cell. When i mouse over the error icon by the cell, it says, "A value used in the formula is of the wrong data type."

However, the cell in the destination cell has basically the same formatting as the source cell, although the border, row size & column size might be different. They are both, for example, "General" in the drop down list on the Number tab of the Format Cells window. this is how every cell in each table is set up except for the cells with dates.

I don't have any hidden columns or any other hidden linked cells. To link a cell, i type = in the destination cell, then choose the appropriate cell in the source workbook and hit enter.

In this example, i'm linking Destination merged cells A49/A50 to Source workbook cells (merged) A1/A2. According to my books and online help, the formula is NOT created correctly:

='ITT Tracking Plan.xls'!$A$49:$A$50

I've tried unmerging the files, but i'm getting the same error. When i choose (double click) the cell in the Source workbook, the correct formula DOES show up correctly:

='[ITT Tracking Plan.xls]ITT Tracking Plan'!$A$49:$A$50

BUT, when i hit Enter to accept the formula/link, the formula changes to what i mentioned above,

='ITT Tracking Plan.xls'!$A$49:$A$50.

For some reason, when i hit enter the reference to the source workbook is no longer bracketed and the source workbook's name relaces the source workbook's sheet name.

Now, it gets weirder, i suppose. If i use the same cells...link Destination merged cells A1/A2 to the Source workbook merged cells referenced the same (A1/A2), the proper formula shows up, BUT in this situation, the formula stays the same once i hit Enter.

Surely, i'm doing something wrong in how i select the other workbook cells or something...why would the correct formula change when i hit enter?

I am using different workbooks. i do have them opened in the same Excel window...if it makes any difference i opened excel, then opened the source workbook from within excel and then the destination workbook from within excel (i.e., i didn't double click on either of the files to get excel to launch).

I'm using Windows XP Pro SP2, fully patched and updated, and Excel 2003 SP, fully patched and updated.

whew...my fingers are tired. Any ideas on how to solve this issue? THANKS!!!

jeffc4442

I have a model that has gradually grown very large. Of course, I've tried all of my usual tricks to shrink it, but it's still larger than I'm comfortable with. (10 MB - difficult to email) Can anyone think of something I haven't tried?

To Shrink Spreadsheets (I've done these)
- Format used cells only, not entire columns and rows
- Delete columns to right of worksheet area and rows below
- Formulas that refer only to necessary ranges, not entire columns or rows
- Replaced 'Indirect' formulas with simpler direct references
- Limited Conditional formats
- Limited use of 'shrink to fit' formatting
- Removed links to external worksheets
- Checked named ranges for error values, duplications or hidden links
- Checked objects and imbedded pictures for size
- I've even extracted the used ranges and pasted them onto a brand-new, clean workbook to be sure there's nothing I've missed.

Can anyone think of anything I haven't tried?
Or know of some utilities that might shrink it?

Thanks

Hi all,
I was wondering if there was any way to adapt the following macro (that I found here:http://www.ozgrid.com/forum/showthre...169#post540169) to suppoert two home pages rather than just one. The purpose of the macro is so that all sheets are hidden except the home page (in my case pages) which contains hyperlinks to all of the hidden sheets. When a link is selected, the corresponding sheet is unhidden and navigated to. When you return back to the home page, the sheet is hidden again.

Here is the code:

	VB:
	
 Hyperlink) 
     
    Dim strSheet As String 
     
    If InStr(Target.Parent, "!") > 0 Then 
        strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1) 
    Else 
        strSheet = Target.Parent 
    End If 
     
    Sheets(strSheet).Visible = True 
    Sheets(strSheet).Select 
     
End Sub 
Private Sub Worksheet_Activate() 
     
    Dim ws As Excel.Worksheet 
     
    For Each ws In ThisWorkbook.Worksheets 
        ws.Visible = (ws.Name = Me.Name) 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.
-CG

Hi Folks,

I'm trying to set up the following situation to help a customer of mine.

They have a data worksheet including hidden rows containing calculation data. They would like to link to that worksheet from another worksheet/workbook and , in this linked worksheet, show the hidden data.

The rationale is that they can see calculation data but their clients (who see the linked TO worksheet) can't.

I'm not aware that this can be easily achieved by the normal linking process but it seems a macro may do the trick?

Any and all help appreciated,

Cart

Hi World,

I am having a problem setting up a Hyperlink to hidden sheets.

My workbook has numerous sheets that link into a master sheet. The master sheet contains a validation (drop down menu) that feeds into a hyperlink formula.

If I were to click on a specific value within the validation (drop down menu), the Hyperlink value changes respectively. Then, if I were to click on the Hyperlink, it brings me to the appropriate sheet. However, if the sheet is hidden, the hyperlink will not work.

Is there anyone out there who can help me with this issue?

Thank you in advance for your time!

Please note: I have posted this question onto another site, but have not been able to resolve the issue. Attached is the link: http://www.mrexcel.com/forum/showthread.php?t=492824

I am wondering how to link from one cell to a cell in a hidden sheet in the same excel file.

Help Appreciated,

- spreadsheet was created on the network and linked to other files on the
network

- I duplicated a tab and deleted data (on the duplicated tab) that I did
not want visable

- duplicated spreadsheet was moved to a separate file and subsequently to
local drive and then emailed to another user with no access to the network

- when the user received the file and copied formula's down the worksheet,
the file attempted to link to the network but could not find file

- the user then clicked "Cancel" & F9 to calc. Data populated as if
connected to the file on the network

The only thing I can think of is there is some sort of hidden data in the
file ?

The solution is to break the links but this is scary when dealing with
confidential information.

Is there anything else I can do, other than break links?

Thx in advance,

Hi

I have a hidden datasheet on Excel. I want a top sheet that links to
this hidden sheet with buttons.

When a button on the top sheet is clicked, the hidden sheet
automatically updates.

So for example a button on the top sheet is 'Number of queries' and
someone clicks it once, the hidden page will have a 'total number of
queries' that updates when the button on the top sheet is clicked.

Does anyone know how to do this??

Thanks

Tilly

--
tillytee1
------------------------------------------------------------------------
tillytee1's Profile: http://www.excelforum.com/member.php...o&userid=27688
View this thread: http://www.excelforum.com/showthread...hreadid=472028

Help Appreciated,

- spreadsheet was created on the network and linked to other files on the
network

- I duplicated a tab and deleted data (on the duplicated tab) that I did
not want visable

- duplicated spreadsheet was moved to a separate file and subsequently to
local drive and then emailed to another user with no access to the network

- when the user received the file and copied formula's down the worksheet,
the file attempted to link to the network but could not find file

- the user then clicked "Cancel" & F9 to calc. Data populated as if
connected to the file on the network

The only thing I can think of is there is some sort of hidden data in the
file ?

The solution is to break the links but this is scary when dealing with
confidential information.

Is there anything else I can do, other than break links?

Thx in advance,

Hi

I have a hidden datasheet on Excel. I want a top sheet that links to this hidden sheet with buttons.

When a button on the top sheet is clicked, the hidden sheet automatically updates.

So for example a button on the top sheet is 'Number of queries' and someone clicks it once, the hidden page will have a 'total number of queries' that updates when the button on the top sheet is clicked.

Does anyone know how to do this??

Thanks

Tilly

It's been quite some time since I got involved in any vba and find myself struggling to relearn what I once knew. That said, what little I did know wasn't THAT extensive -- I've spent days trying to adapt various examples to my needs with little, if any, success.

My initial thought/goal was to script some vba to concatenate a number of ranges and copy the output to two sheets, but that proved too troublesome due to the number of columns columns and the amount of code/skill needed, so I gave up on that pipedream. I've decided instead, now, to run formulas across several hidden worksheets linked to another workbook containing the data and then copy the concatenated values (it's only five columns, but needs to be selectable by row/range of rows) from the hidden worksheet to the two other worksheets, one clears the data each time the script is run, the other to find the last line and drop the same values for a historical log.

I've had only one of the last eighteen attempts work halfway; Several times I was able to run it once, but upon running the script again, it would error out. I was bound and determined to not let this beat me, but I've wasted more time at work and two and a half days of my three day weekend searching and at this point, I'm fresh out of steam -- and I hate myself for it.

Anyone have any suggestions on how to approach this?

Edit: Incidentally, the only other requirement I have, given my second choice/lazy approach, is that another worksheet remain in focus when the script is run to use, as a cell reference, a copy of the linked workbook data. Optimally, it would not be selectable, but I can work that out later...

Here's what I''ve managed to piece together. Sorry for the hatchet job. If anyone can assist, it would be greatly appreciated.


	VB:
	
 Worksheet) 
    On Error Resume Next 
    Lastrow = sh.Cells.Find(What:="*", _ 
    After:=sh.Range("A1"), _ 
    Lookat:=xlPart, _ 
    LookIn:=xlValues, _ 
    SearchOrder:=xlByRows, _ 
    SearchDirection:=xlPrevious, _ 
    MatchCase:=False).Row 
    On Error Goto 0 
End Function 
 
Sub userin() 
     
     'Dim uiRange As Range
     'Dim inputValues As Variant
     'On Error Resume Next
     'Set uiRange = Application.InputBox("Input a range.", Type:=8)
     'On Error GoTo 0
     'inputValues = uiRange.Value
     'Rem manipulate inputValues
     'ActiveCell.Resize(uiRange.Rows.Count, uiRange.Columns.Count).Value = inputValues
     '
    Dim vArrIn As Range ' input range
    Dim vArrOut As Range ' output range
    Dim oStart As String ' Starting cell of output range
    Dim oEnd As String ' end cell of output range
    Dim columns1 As Integer ' number of columns in array
    Dim rows1 As Integer ' number of rows in array
     
    Set vArrIn = Application.InputBox("Select Range", Type:=8) 
    rows1 = vArrIn.Rows.Count 
    columns1 = vArrIn.Columns.Count 
    oStart = ActiveCell.Address 
     'oEnd = ActiveCell.Offset(rows1 - 1, columns1 - 1).Address
     '

Reafidy has been a great help with the code provided.


	VB:
	
 1 
Private Sub CbxSelectAll_Click() 
    Dim i As Integer 
    For i = 1 To ListBox1.ListCount 
        Me.ListBox1.Selected(i - 1) = Me.CbxSelectAll.Value 
    Next 
End Sub 
Private Sub CbnCancel_Click() 
    Unload PrintOptions 
End Sub 
Private Sub CbnPrint_Click() 
    Dim i As Integer, j As Integer 
    Dim a() As Variant 
    On Error Goto Errhandler 
    Application.ScreenUpdating = False 
    For i = 1 To Me.ListBox1.ListCount 
        If Me.ListBox1.Selected(i - 1) = True Then 
            j = j + 1 
            Redim Preserve a(j) 
            a(j) = Worksheets(ListBox1.List(i - 1)).Name 
        End If 
    Next 
    Worksheets(a).Select 
    Me.Hide 
    Application.Dialogs(xlDialogPrint).Show 
Errhandler: 
    Worksheets(1).Select 
    Application.ScreenUpdating = True 
    Exit Sub 
End Sub 
Private Sub UserForm_Initialize() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
        If ws.Visible = True Then 
            ListBox1.AddItem ws.Name 
        End If 
    Next ws 
    Me.CbxSelectAll.Value = True 
End Sub 
Private Sub UserForm_Terminate() 
    Worksheets(1).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have been busy trying to add some other components to this project and now I need to add something into this print userform menu that has me stumped.

My situation is a questionnaire that has some pages where open ended text responses are captured using textboxes. Because there are additional questions below the textbox, the size is set to fixed with a scroll appearing to add/view text beyond the window. However, I have found no way to print this without the text overlapping the questionaire information below.

As a workaround hidden worksheets been created with textboxes that are linked to the ones where the respondent enters the text. For example, if textbox1 is on worksheet "Page 6" then there is a hidden worksheet "Page 6(1)" where there is a textbox sized to display the whole text.

Wanting to keep these hidden sheets from the respondent, what I would like to do is have the print listbox from the code above also print worksheet "Page 6(1)" when "Page 6" is selected from the listbox.

Any suggestions would be greatly appreciated.

In Excel can you link comments? For example…. I have comments in some of my cells, I have it set as indicator only so when my mouse is over the cell the comment will appear. When I move my mouse over A1 I want it to display A1 comment and say E1 comment at same time (but leaving the other comments hidden). Is this possible?

I have this posted here, but have had no luck

http://www.excelforum.com/showthread.php?t=647736

I was wondering if it is possible to update links from one worksheet to another that contains a running tally - even when another worksheet is added and needs to be linked to the running tally as well?

I have attached a sample workbook. What I have is a summary sheet (Sum Hrs CMS) that has SUMIF calculations for a series of rows in the Estimate Sheet.

The problem that I have run into is - when you press the Add New Sheet button on the Estimate sheet, it copies hidden sheet and inserts it at the end of the workbook, however I then need the hours in the CMS portion to link to the summary sheet. What I was wondering is how do the links on the 'Sum Hrs CMS' sheet include this new sheet and the data that is entered on it? Can this be done?

Thanks
Jimbean

Hi Everyone,

I have been trying to do a search to find my answer but I dont seem to find what I need so maybe some can help me. I have a large Workbook with 290 tabs referencing 2 worksheets within the same workbook.

I need to save into a new workbook each of the tabs but I also need to keep the lookup links to the 2 other worksheets.

So I believe I need a macro that will loop thru the worksheets, save the workbook as the current worksheet name , delete all the other sheets except the current worksheet and the hidden sheets and save as the current worksheets name.

Thanks in Advance for any assistance.

Teresa

Does anyone know if it is possible to hide a worksheet but still allow a cell link to open it up?

I want to hide many worksheets to simplify things with cells that users can click on to view the data. As soon as i hide the worksheets the cell links no longer work.

Any way to do this?

Help is always appreciated,

Thanks

Ok, here's my problem I need to hide columns in a spreadsheet without using the hide/unhide columns option. I found an article which suggested setting up a combobox from the forms toolbar, this is populated by an input range and linked to a cell for a numeric value. The article although using a macro to hide rows suggested this code


	VB:
	
 ComboBox1_Change() 
     
    If ComboBox1.Value = "A" Then 
        ActiveSheet.Range("1:3").EntireRow.Hidden = True 
        ActiveSheet.Range("4:6").EntireRow.Hidden = False 
    ElseIf ComboBox1.Value = "B" Then 
        ActiveSheet.Range("1:3").EntireRow.Hidden = False 
        ActiveSheet.Range("4:6").EntireRow.Hidden = True 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to amend this code so that when the number in my linked cell changes the various columns are hidden.
For example if the cell link number = 1 hide columns e to az
if the cell link number = 2 hide columns d and f to az etc.
I have had varying success with amending this code to entirecolumn.Hidden and changing the Combobox1. to dropdown1. but am unable to get it to work, I also stupidly deleted the code that was partially working and am now unable to remember what I'd done.
I have attached an example sheet if anyone can help.

Cheers
Wheelie


No luck finding an answer? You could always try Google.