Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Re-populating Hyperlinks that have been cleared

Hi everyone . . . Don't know where this should go, but I think I need alot of power on this one.

I have a strange one. I have a worksheet that I have removed a good number of hyperlinks, using the remove hyperlink function. My problem is that every so often, the hyperlinks are back on the sheet, without seeing any wording for the hyperlink.

Some place, a long time ago, I saw that there was something in the program that either stopped this from happening or turned the function off or a way of restoring removed hyperlinks. Today, I can't find it.

If I run my cursor over the spreadsheet, even over blank cells, I will get the evil finger of fate (so to speak) indicating that there is a hyperlink in that cell and yes, there is. If I click the cell, the site will be activated.

How do I keep these removed hyperlinks from returning.

I've also noticed that at anytime, while working on the sheet, everything flickers just as if the links are being re-populated. After a second or two the flickering stops and yes, the links are back.

I hope someone can help me. It's just as if there is a macro that runs in the background at anytime.

I have no macros that would fire automatically.


Post your answer or comment

comments powered by Disqus
I have a multiage customer selection sheet, choosing paint color, cabinets, doors lock-sets etc. Workbook is broken down into sheets representing each room of the house. What I want to do is make one sheet of the workbook a consolidated sheet of the customers choices. The tricky part is that I only want to populate the sheet with lines that have been chosen . Any ideas.

Hello bos, i want to hide rows in accordance with the line numbers that have been determined with Macro, on G3 (Start Rows) and H3 (End Rows).

And i have attached my example file on excel (.xls)

Thank's before..

In Excel XP, is there a way to tell if or when a cell has been updated?
We have worksheets that are sent out for a Budgeting process to all division
managers.
They input their numbers and then send them back to the controller. The
changes are merged back together to the master workbook.
Certain columns of information are then extracted and exported to our SAP
accounting system. This process may be repeated several times
Each time the worksheet goes out and then back the only items I need to
re-send to SAP are the items that have changed but I have no way of knowing
what lines or cells have been changed.
Is this an inherent function of Merging Worksheets or is there some other
technique that would work better?

Dave French

I have a template I use on a daily basis, this is totalled in a summary sheet for each invidual week.
In the summary sheet I have the formula =SUM(Sheet1:Sheet5!A1).
Sometimes the week may only have four days in the workbook which would mean having to change the formula to =SUM(Sheet1:Sheet4!A1)
What formula can I use in the summary sheet to count only the sheets that have been inserted to date?

Thanks in advance

Is there anyway to flag a cell in worksheet 'questions' that has been referenced in worksheet 'test'. Example: In worksheet 'test', cell D3, I enter =questions!C15. I want to know that cell C15 has been used in worksheet 'test'.

There are macros that have been programmed into a worksheet that I copied.
These macros are assigned to start-up when I open the worksheet. I then
received a warning message that the macro cannot find the file that it has
been assigned to access at start-up. I do not need the macro and would like
to delete it, however, when I click on the macro it tells me the the
reference is invalid. The delete key for this macor is not highlighted which
prevents me from deleting it from my worksheet.

Any help would be sincerely appreciated.

I have several hyperlinks in a worksheet and I know you can format it so that
it changes color after you click on it to show help you distinguish between
the hyperlinks that have been used and the ones that have not. But I can't
remember how.... any thoughts?

When charting a large range of cells, which have been grouped in sets of 12
for convenience and easy review, the chart using this data is blank. If I
ungroup the cells, the chart shows the data.

Is there a way around this? Why can't a chart show the data even if the
data is hidden?

I have a list of hyperlinks in column 5 that I want to open and then automatically close. If the hyperlink is invalid (can not be opened), I want to flag it as an error. The hyperlinks are being opened, and invalid ones are being flagged as "error" but the opened URL's are not being closed. As a result the "ActiveWindow.Close" code is actually closing the worksheet and not the URL that has been opened. How can I close the opened URL?


	VB:
	
 HYPERLINK_Open() 
    Application.Goto Reference:="R2C6" 
    Do 
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Offset(0, -1).Range("a1"), _ 
        TextToDisplay:="Hyper link" 
        On Error Goto errhandler 
        ActiveCell.Hyperlinks(1).Follow 
        ActiveWindow.Close 
         ' This is not closing the opened hyperlink... as a result it is closing the worksheet.
        ActiveCell.Offset(1, 0).Range("A1").Select 
    Loop Until IsEmpty(ActiveCell.Offset(0, -1)) 
errhandler: 
    ActiveCell.Offset(0, 1).Value = "Error" 
End Sub 

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

Hi all, I have to create two invoices and a total monthly (one in each sheet) this with a growing total on the third sheet. When someone populates the first invoice I want that data and populate the third spreadsheet, and the data from the second to also populate the third. After this is done the fields on sheets 1 and 2 will have the contents cleared for the next input. I need to keep the data information captured in the third spreadsheet while the totals are moving down the 3rd sheet (growing balance) with the date and other information from the 1st and 2nd spreadsheets so at the end of the month I would have a list of activities and running totals from each invoice. Thank you for your help Ken

Hello,
I am using Excel 2007 with windows vista, but have saved the excel spreadsheet as compatible with 97-2003 because my kids use older versions, and still have windows XP.

I please need help with code that copies data if no entry has been made as indicative by not selecting rows that still say Enter Here text. .

I have created an excel spreadsheet to track peoples birthdays and anniversary dates.
In Row 6 Columns A through E has the following column header.
Enter Name or Anniversary Names / Enter Birthday or Anniversay / Current Age /Age Next
Birthday / Days to Next Birthday or Anniversary

Its set up such that in Col A and B it states “Enter Name or Anniversaries Name” in Col A and “Enter mm/dd/yy” in col B, both have conditional formatting to have a yellow fill and Red bold lettering as long as it states this text. Where by when a new person is entered to track, the formatting goes back to nor color or special font.

After an entry is made in col A and B, besides the conditional formatting change, columns C, D & F change from the word “Value” showing now, and populate based on formulas relating to todays date (which is created in cell A3 using formula =TODAY(). This creates the current age, age at next birthday, and days to next birthday or Anniversary.
I currently have 25 entries in rows 7 through 31 with fixed birthday data, they are grouped by family’s.
Rows 32 through 125 are set up for additional entries if needed, still stating to “Enter Name….etc

Originally I just had all my family listed and I sent a copy to all my kids, and my inlaws to use for tracking.
I added the extra entry rows so the kids could add their inlaws etc.

Then I came up with the idea of creating a macro button that will copy the rows with entries (ie starting with Row 7) and pasting, and sorting them by the “days to next brirthday or anniversary”
I want to paste the sorted data under these additional column headings in Row 6 Col G-K (which is just to the right of the fixed data)

Name /Birthday / Current Age /Age Next Birthday /Days to Next Birthday or Anniversary

So I recorded this macro that starts with deleting any old data that was pasted and sorted.

Sub CopySortBirthdayDates()
'
' CopySortBirthdayDates Macro
' This first deletes previous copied and pasted data, then copies the fixed data in column A-E. Pastes this data in Col G-K. Then sort it by the "days to next birthday or anniversary” in Col K

Range("G7:K165").Select
Selection.Delete Shift:=xlToLeft
Range("A7:E31").Select
Selection.Copy
Range("G7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort.SortFields. _
Add Key:=Range("K7:K31"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Birthdates and Anniversarie (2)").Sort
.SetRange Range("G7:K31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A7").Select
End Sub

This basically copies the rows of data I have and pastes them in Cell G7 Then it sorts all the pasted data based on col K (Days to next birthday or Anniversary.

I want to send this to my kids, they are less literature when it comes to excel formulas and macros than I am. The problem I have and need help with if you guys would be so kind is
1) So when they push the macro I made it will only copy the cell/rows I have entered data for (Row 7-31 or Range("A7:E31") ). I am hoping you can help my macro code to copy the rows that have data in them, ie Rows 7 to 125 ,the ones that do not say “Enter Name or Anniversaries Name” in column A. Such that no mater how my new entries the kids make it will copy,paste and sort all of the ones they have data in.

Please advise.

Sorry to make it so lengthy.
Thanks
Keith

Out of curiousity, is there a way in excel to notify a user that records have been deleted from another workbook?

The situation is that I have multiple 'servant' sheets that copy records designated 'closed' to one 'master' workbook. These records are then purged from an archive tab (copied to another workbook and deleted from 'archive' in master). I need a way to let owners of the servant sheets know that these records have been purged, so they can delete them from their queue. It would be great for the user to recieve a message box saying something to the extent: "Records 1,2,5 have been purged. You may delete these records"

Is this possible?

**Revised**
As it turns out, the goal of this project is a bit different than previously stated. The user wants the ability to change records (once imported from servant sheets) from 'complete' to 'closed'. Once she designates 'closed', THAT is when the servant sheet should see a message saying 'record 1234 has been closed. You may delete this record'.

Basically, is there a way to say that:

When: record number 1 on master spreadsheet = closed
Then: Notify servant sheet they may delete that record.

Hello all,
I am new to the forum, but have viewed many of the threads in the past to hone some of my knowledge. I currently have a worksheet which I need to add a hyperlink to a cell value when the cell changes.

Code:

	VB:
	
 Range) 
     ' Select Cerner error spreadsheet
    Sheets("Cerner Errors").Select 
     
    Dim finRange As Range 'All the values in the C column
    Dim finVal As Range 'The cells that contain values
    Dim finNum As Variant 'The patient's fin number
     
     ' Set the range to be all the cells in the C column that contain values
    Set finRange = Range("C1", "C65536").End(xlDown) 
     
     'Loop through the column attaching hyperlinks to the word docs containing screenshots
    For Each finVal In finRange 
        If WorksheetFunction.Count(finRange) = 0 Then ' if there are no cells in the list
            Exit For 
        ElseIf finVal  "" Then ' if the current cell is not empty
            finNum = finVal.Value 'finNum is the pt. fin number
             'Create the hyperlink to the word document with the finVal as the document name
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _ 
            [URL="file://dbcfp1_w.billingsclinic.orgVOL1sharedPharmacyCerner Error
Images"]dbcfp1_w.billingsclinic.orgVOL1sharedPharmacyCerner Error Images[/URL] & finVal.Value & ".doc" 
        End If 
    Next finVal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I currently get the value entered into the cell, but the hyperlink is created in the cell below (if the value is entered into C2, the hyperlink is in C3).
I would like the number entered into the cell to be the hyperlink.
i.e. if 12345678 is entered into the cell, when it is clicked, it should take you to the word document

Hopefully I have been clear, if not please let me know!!

Thanks,
nate

Afternoon all, first off thanks for the tips and help that I've managed to glean off of this board. I've hit something that is annoying me that I couldn't solve with the trusty search button and was wondering if I could get a bit of assistance.
I've got an excel sheet with 3 cmd buttons on it, one brings up a form which allows the user to add a number of "actions". For each action a new worksheet is created as a template. The second button brings up a form which allows the addition of "elements". These are basically just rows of information about a circuit element. Then the third button brings up a form that has 2 comboboxes that have been populated by the addition of actions and elements. One of each is selected and then the action template is copied with the elements name. That all works wonderfully, my issue is with the combobox lists.
When I go in and out of the different forms everything works well, however when I close or save+close the spreadsheet and re-open it and attempt to continue working on it, both comboboxes are cleared. Now, I do believe that excel will reinitialize variables on its start-up, does this apply to dynamically generated combo and listboxes as well? Would I be best to attempt to store the information in the comboboxes in a list on another worksheet on close, and then on opening the sheet back up again, re-populate them? That seems silly to have to do, and plus I'm not entirely sure how to do that . Thanks in advance for any assitance!

I have a spreadsheet that has been running just fine in Excel 2003 for many years. The spreadsheet contains some XY charts as separate worksheets.

I have noticed in the past 2 months that if I resave the files that have been running well, the next time I open it, I get a serious error and the file will not open.

I have traced this problem down to one of the worksheets in the workbook. It is a XY chart which is on a separate worksheet.

I am not sure what it is about this chart that Excel suddenly does not like. The only thing odd about this XY chart is probably that at some point 5 years ago, I manually adjusted the borders of the chart to make the aspect ratio look more like a 1:1 ratio. I am plotting graphical data that looks realy bad if it is not at a 1:1 aspect ratio.

When I delete the cart the file opens fine. I am in the process of re-creating the chart, but of course not knowing what was wrong with original one kind of make the process hit and miss.

I am pretty sure that it must be comng from a recent service pack, because I have 3 computers that run the same files. Each one independently has been having the same problem - although the timing seems to have happened only after either a Windows XP or MS Office Service pack was installed. I am not sure which one is creating the problem.

I have been in contact with Microsoft Excel tech support. All they can tell me is that the original file is fine, and the resaved file has become corrupt.

Any hints on how to figure this one out?

I would like to create a floating toolbar within an excel (word, access,
etc.) document that would allow me to access hyperlinks within the same
document. All I have been able to do is create a menu link that will access
a document outside of the current document. Is there some way to achieve
this result.

Excel 2007

Steps taken: Created a certificate called XYZ (which I thought enable my Excel Macros)
which was placed in my Personal Certificates Store but forgot how I did that a year ago.

Now almost every previously created Excel w/s I open contains the message:

"Data connections have been disabled"

Bottom line - how do I get around this issue??

Also, I have deleted all data connections (in a workbook), saved the file, then re-opened
it only to find the same data connections.

ADDITIONAL STEPS:

I have heard that the solution may involve the MS Certificate Manager.

Therefore I used "certmgr.msc" (from START>RUN) certmgr.msc and I would like to install
my certificate into "the trusted Root Certification Authorities store".

I can see the certificate in the Personal folder but its "certification path" indicates
that it is "Not Trusted."

Do I have to delete it and start over? modify it?

I have no clue how to:
1) create, modify a personal certificate and/or
2) install it in the trusted Root Certification Authorities store and/or
3) make sure that the "certificate path" is OK (in short authorized and OK)

TIA EagleOne

Hello,

I have a multipage userform to record job costing details. The form allows the user to input and then save that data to a spreadsheet. The job is identified by a job number (entered in a textbox) and also a job suffix (also entered in a textbox). Once all the initial information is entered on the multipage userform and submitted to the spreadsheet, how do I then re-populate the userform (for updating or amending) with that row's data by searching for it by entering the job number and job suffix?

I've managed to retrieve a record based on one field/textbox, but this requires a search or match on two - and I'm stuck.

Any help or pointers much appreciated.

Kind regards,

Mark

Hi

I hope you can help

The attached workbook has, in effect, two different tables on the sheet

Table A has a list of both lecturers and students in the same list. The list can be auto-filtered to give either the list of lecturers or the list of students.

class occurrence
lecturer lecturer1
lecturer lecturer2
lecturer lecturer3
student student1
student student2
lecturer lecturer9
lecturer lecturer10
lecturer lecturer11

Table B shows which lecturer has been allocated to which student.

lecturer student
lecturer1 student2
lecturer2 student2
lecturer3 student1

At the moment, the lecturer and student cells have data validation set to look at fixed ranges of lecturers and students in table A

The problem is that the static defined list only includes the first three lecturers. I have added three new lecturers after the student entries. What I need is for the data validation in table B to change from static ranges to a dynamic AUTOFILERED ranges. (i.e. not a consecutive set of rows).

Is this possible?

Thanks. Any help would be very much appreciated

Hi guys,

I have a small query; I need help populating a powerpoint with figures that have been extracted from a database onto an excel spreadsheet. The powerpoint is 90 slides long, 4 sets in total. Both the powerpoint and the spreadsheet are templates, so how we're doing it right now is manually copying and pasting from the excel spreadsheet to the database. The aim is to automate the process; have heard that VBA can do the job.

Please help =*(

Hi all,

Firstly, thanks for all you have done for me in the past.
I need your help again. I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet). The date at the top is editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it. Maybe that isn’t the way to go. In any case, I am at a loss for ideas.
Any ideas, suggestions and/or solutions would be greatly appreciated.

Cheers
Jeff

I am currently working with a "client_roster" list, (Last Name, First Name - column A, column B), another "populate_frequency" sheet that adds column C (visit Frequency Drop down list), and 5 additional sheets (each sheet dedicated to the clients that visit with each frequency).

I need to be able to populate new rows in the "populate" sheet that have the First and Last names injected into columns A & B (respectively) of "populate_frequency" from "client_roster", also continues the drop down list (column C) on the new row.

I need to be able to macro this so that when a tech exports their client list (client roster), they can hit a macro button on the toolbar to inject only their new clients (non-existing in "populate_frequency") into "populate_frequency" on a new row (alphabetical by last name would be even better) that also includes the drop down list in Column C. I have seen enough tutorials to figure out how to inject each client based on their selected frequency into the appropriate sheet for that frequency, I just need to automate the client list sheet -----> populate frequency sheet process.

A simple copy/paste or insert copied.... does not preserve the functionality of column C, the new cell in this column is just a blank cell. One solution that I have now is to preformat column C for 200 rows, and they can just copy their client list into Columns A & B, but this would not work for only injecting new clients, and would require Column C to be re-entered every time they update their client list.

I have a feeling this is some kind of IF loop, but I haven't really used macros enough in Excel to be able to tell the difference.

Thanks in advance, I will check back to see if I need to clarify anything.

Update:

I have been experimenting and reading as many tutorials as I can find. I've almost resigned myself to just pre-format "populate_frequency" with about 1,000 rows with empty drop down boxes in place. However, I would still like a macro to inject (populate) the unique last name and first name columns for each client in "populate_frequency" list. Also, I am trying to ensure that if there are clients who are no longer on the "client_roster", thier row is removed from "populate_frequency".

Any suggestions on macro code to accomplish this?

Hi
I need to work out a sumproduct but can't find the right order to get the formula right. I need to work out how many days a cases have been outstanding from the Decisons worksheet on the date shown in Weekly summary worksheet Cell A5.
(date case cleared) Column D5 : D6000 must have no info input (date),therefore a blank cell.
(date case received) Column A must have info put in it (date).
(NC is a initials for type of case) Column K5:K6000 must have NC entered in it.
(has the amount of days case has not been cleared from date of receipt)Column J5:J6000 is to be added up if the above apply.
Hope you can help
Here is a formula that finds how many cases not cleared, might give a clue to what I'm doing:

=SUMPRODUCT((DECISIONS!$D$5:$D$6000>'WEEKLY SUMMARY'!A5)*(DECISIONS!$A$5:$A$6000<='WEEKLY SUMMARY'!A5))+SUMPRODUCT((DECISIONS!$D$5:$D$6000=0 )*(DECISIONS!$A$5:$A$6000<='WEEKLY SUMMARY'!A5)*(DECISIONS!$A$5:$A$6000>0)*(DECISIONS !$K$5:$K$6000="NC"))

Hi all,

I am using excel 2003 and some cells when I input an email address do not
automatically turn into a hyperlink. The hyperlink funtion is grayed
out/disabled when I try to manually insert a mailto link. I have checked the
cell format and the cells are the same as the ones that hyperlinks are
working. Hope I have been clear!

Regards

Aaron Browne


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