Hyperlinking Files With A Portion Of Name

Apr 15, 2014

I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.

View 7 Replies


ADVERTISEMENT

Hyperlinking To PDF Documents

Apr 28, 2009

This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.

Sub OpenPDF()

Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String
fName = ActiveCell.Value
fPath = "M:Books"
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............

View 9 Replies View Related

Absolute Hyperlinking Within Worksheet

Jul 14, 2014

In trying to make an alphabetized record of all inventory for work, I ran into a snafu when creating shortcuts for other users to the different letters.

I hyperlinked something like a table of contents together and hyperlinked them to the cells, so clicking on B in the table of contents takes you to cell B64. I want to future-proof it with absolute references but haven't been able to.

I tried right clicking and typing in the cell name--but when I add more cells, instead of moving to B72 like it should, the hyperlink stays in B64, which is now part of the As.

I also tried: =HYPERLINK(A$64,"B") as well as =HYPERLINK(B_Start,"B") by naming the cell. The name went to B72, but the link stayed in B64

View 3 Replies View Related

Hyperlinking To Specific Number On URL

Sep 6, 2013

My URL is a javascript page. which looks something like this:

HTML Code: 

var quantity_in_stock_S = 2
var quantity_in_stock_M = 2
var quantity_in_stock_L = 1

and I want to hyperlink the numbers to individual excel cells

How can I do this?

View 1 Replies View Related

Hyperlinking Info From Another Computer

Jul 1, 2006

In my restaurant we have a master computer upstairs that collects all of the register sales info. Then we have a computer downstairs that my employees sign in and out on.

This excel timesheet keeps track of the amount of labor hours being used per day. Is there a way of doing this without putting the timesheet on a web page or putting my sales info on a web page?

View 4 Replies View Related

Hyperlinking Texts In Hierarchy Chart?

Jul 2, 2013

I have created a hierarchy chart using smartart. Is there a way to hyperlink the texts in each box to other sheets in the workbook?

[URL]

View 3 Replies View Related

Hyperlinking With Address And File As Variables

Mar 16, 2009

I'm trying to Hyperlink my documents to a folder and file name specified by different cells in my workbook

Or essentially, I'm trying to go from this...

View 14 Replies View Related

Hyperlinking Directly To A Specific File?

Jul 6, 2009

I'm having trouble creating a hyperlink that will automatically take a value from a cell and link me directly to a file of the same name across the company server.

For example in column B I have a number along the lines of

43FG2345678

In a folder on the server I have a word document that goes by that exact file. I need to create a hyperlink that looks up that value and searches for that specific document in the location file, and then directly open that file.

The value in column B will change with each line so as I copy the formula/hyperlink down I want it to automatically go in and replace the text and then lookup the file.

I have tried the concatenate function within the hyperlink but not had much luck.

View 2 Replies View Related

Hyperlinking Text (paste Special On Some Links)

Feb 26, 2008

I have a problem in that in using the paste special on some links I created using HYPERLINK/VLOOKUP formulas the hyperlinks themselves have gone (ie nothing happens if you click on the data).

What I want is a macro that can turn a cell's contents into a hyperlink (i.e the location and friendly name are the same) by means of a simple keyboard stroke. I tried doing this using the macro recorder, but it didn't recognize when I was trying to paste the text into the Insert Hyperlink address bar and it just recalled the name of the address in the VBE window as if I'd written it in manually.

View 9 Replies View Related

Hyperlinking Multiple Cells Based On Another Cell Contents

Feb 23, 2012

I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.

The setup
*I have a folder called Zircon Plant
*That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders)
*The Register is located in 03. Documentation
*The PDFs i want to hyperlink to are located in 02. PDFs

What i want to do
*i have 2000 rows of drawing names
*the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21)
*the cells i want to contain hyperlinks are V21 - V2020

so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.

(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)

If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"

based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is

' Select PDF range
ActiveSheet.Range("u21").Select
' Promt PDF Hyperlinking
ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"

I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets

View 5 Replies View Related

Sum Portion Of Array Without Loop

Jun 24, 2009

I faced a problem to attach this small WB as an XLS

I'm looking for some way to some the red cells without looping.

In this example the array was filled with A1:A10 values.
In the real situation the array gets its values from other source than a Worksheet Range.

As you can see I manged to transport the Array Values to F1:F10 and from here I could calculate the sum of F3:F8 but I do not want to use any helper columns.

View 12 Replies View Related

Sorting From Portion Of Cell

Feb 26, 2009

I'm trying to sort a column of email addressees to determine how many have the extension .ac - basically I need to know how many student addresses are present in the column.

View 9 Replies View Related

Protect Portion Of Sheet

Oct 13, 2009

I find the protection options of Excel confusing. I have a pivot table. Alonside it our a few formula columns. I want to protect only the columns outside the pivot but can not get it to work. I tried this:

1) Highlight entire sheet
2) Format Cells Protection - remove checkmark from: Lock Cells
3) Highlight 4 rows outside pivot
4) Format Cells Protection - place checkmark in Lock Cells
5) Tools Protection Sheet - supply password

The result is that the 4 columns outside the pivot are indeed locked BUT SO IS THE PIVOT...ie you can not utlize the dropdowns!

It must have something to do with the pivot, on a regular sheet (no pivot) it works fine. Further if, while Protecting the sheet, I click the: allow Pivot table reporting box, then it allows dropdown usage but blows up as soon as you select something with an error about not being able to redisplay selected itemsbecause of protection being on....

View 9 Replies View Related

Lookup For A Portion Of A Text Value

Dec 16, 2009

I want to lookup a text value in an array (vlookup). However, instead of searching for the exact value I want to search for only the exact match of the first four characters of the text value. Example

If I have in Cell A1 the text value: 5154
In cell B1 I want to lookup 5154 in the values cells C1 to C6
614a215g
515476
29421cg
215a786
914215g
2154a6

The result would be 515476

I tried with this:

=VLOOKUP(A1,LEFT(Table4,4),1)

View 9 Replies View Related

Extract Portion Of Address

Jan 5, 2007

I have read Dave Hawley's reply to a member using LEFT and MID but I can't seem to make what I want, to work.

I have an address in the follwing format:

12 MAICH ROAD, MANUREWA CENTRAL, MANUKAU CITY

I need to extract the '12' to be placed in one cell, and the 'MAICH ROAD' in another cell.

Unfortunately not every address has a street number and is sometimes listed as follows:

LEYTON WAY, MANUKAU CENTRAL, MANUKAU CITY

I still need to be able to put the 'LEYTON WAY' portion into the same column as the 'MAICH ROAD' in the previous example.

View 3 Replies View Related

Return Decimal Portion Of Number?

Mar 22, 2008

how do i get the numbers after the point.

View 9 Replies View Related

Transferring Portion Of Date From One Excel To Another?

May 28, 2014

Transferring a portion of a date from one excel to another. On the file attached, the 1st column lists the date, and the second column needs to show the date in text format.

3-Jan-14 —> “January”

How this can be done?

Attached File : Month Problem.xlsx‎

View 4 Replies View Related

Possible To Ignore Text Portion Of Given Cell

Jul 26, 2013

I was wondering whether it is possible to have excel ignore the text portion of a given cell. Or alternatively, have excel add in units (or specified text) after you input an initial value so that the cell can be used in a formula.

I have noticed that if I have cell A1 as 1 min and autofill, excel will recognize the number value and still put 2 min in A2, 3 min in A3, etc. However I can not use a function or any formula as the result will be #Value or another error.

I am looking for a way to be able to use the cells in formulas, and I do not want to put the text portion of the cells into another row/column.

View 3 Replies View Related

Save Portion Of Worksheet In PDF And Email Out

Jun 26, 2014

I have the following code in VB from somewhere. How to modify or adding from the following codes.

(1) Is anyway I can modify sedning out PDF instead of the actual workbook?
(2) Is anyway I just want the range from (A1:G80) instead of the whole sheet for printing (PDF).

View 3 Replies View Related

Copy Portion Of Filtered List

Jul 7, 2009

I need the VB code to copy just a portion of a filtered list. I have completed the code to sort and filter the list. I'm having a problem determining how to define the region needed. I have searched the forums and found a few helpful threads but nothing specific. Most show selecting all the columns of the filtered list.

My list is in columns A:AA and begins in row 4 (header row). In my test data, there are 5,900+ records and filtered list is approximately 4,900 records. Since I have sorted the data, the portion of the filtered data I need will always begin in cell D5, be columns D:K, and be the visible rows.

View 4 Replies View Related

Sort By Portion Of Data In Column?

Mar 19, 2012

Is it possible to sort by column, but only a specific portion of the data within the field?

For example, I have a set of numbers, 5026125120. I want to sort my spreadsheet by the 3rd and 4th digit only of that set of numbers (5026125120).

View 1 Replies View Related

Formula To Take Number And Add One To The Portion Before Hyphen

Mar 22, 2012

I am in need of a formula. I have a number like: 23847-2

I want a formula to take this number and add one to the number before the hyphen. I also want the single digit number after the hyphen to be able to be changed.

So in the end: i want the number copied to the next cell, add one to the portion before the hyphen. Then allow the number after the hyphen to be changed manually.

View 7 Replies View Related

Leave Portion Of Text Formula

Jan 27, 2014

C4 - #01941 (ATF Cleaner)

I have asked this before as to a formula to get rid of the text and only leave the #01941. The formula I received is =LEFT(C4,FIND(" ",C4)-1) and it works fine.

My queston now is there a way to only leave ATF Cleaner? No ( ) around it?

View 4 Replies View Related

Extracting Portion Of Text From Cell

Nov 16, 2006

I have made something similar before and we were able to make it work... I tried the same code, but it is not working now. Basically in cell a1 I have

Auction Type: Auction, Auction End Time: 11/15/2006 10:00:00 AM(MST), Asking Price/Current Bid: $10.00, Number of Bids: 0, Description: , Traffic: 28
and in cell b1 I need it to only display $10.00 in cell c1 I need 0 (for number of bids)

View 9 Replies View Related

Extract A Portion Of A Text String

Jan 1, 2007

I have a text strings that contains the word "CHQ 123456" or some other CHQ number. There might be another word/words after the word CHQ xxxxxx something like:

Payment by CHQ 123456 against your inv 45225
ABCD Company Limited - CHQ 187546
PO 4520061257/CHQ 745865/invoice Number 4125

I need a UDF that extracts the CHQ No. from the text string
Eg:-

CHQ 123456
CHQ 187546
CHQ 745865

View 9 Replies View Related

Formatting A Portion Of Formula Results

Aug 28, 2007

I have the following formula (basically a concatenation of a few different cells):


=R2&", "&Q2&" {"&U2&"}"&" ("&V2&" Days)"
I was hoping to make bold and maybe larger font the " ("&V2&" Days)"
portion of it...
is that possible?

View 9 Replies View Related

Extract Portion Of String Using Formulae

Apr 11, 2009

If I have a string of the form:

S:GenerealfoldereferenceFolder2FolderTest_ABC_2009_v2.xls

I would like to extract the ABC term, i.e. the term that liues in between the 2nd and 3rd "_" in the Workbook name (in the above example "Test_ABC_2009_v2.xls")

View 9 Replies View Related

Return Decimal Portion Of Number

Mar 22, 2008

trunc gives just the numbers before the decimal point. how do i get the numbers after the point. original number minus trunc seems messy!

View 3 Replies View Related

Highlight Portion Of Text Of TextBox

Mar 27, 2008

The following code highlights part of the time (hours or minutes) that will be modified by a spin button. The code works fine (although maybe a more efficient way). Each time you click in the time it will highlight the hours or the minutes, depending on where you click. When you click on the spin button the control loses the focus so becomes un-highlighted. I thought that just setting the focus back to the text box would work but what I am getting now is it highlights on every other click of the spin button.

Private Sub spnTime_Change()
Dim dtTime As Date
Dim y As Integer
If strTimeChange = "" Then
MsgBox "Please click on a time to modify it"
Exit Sub
End If
dtTime = Format(ctlText, "hh:mm")
y = Me.spnTime.Value...............

View 2 Replies View Related

Macro To Copy The Portion Of Text In Cell?

Jun 2, 2014

write a macro to copy a set of sentences in an Excel Cell.

Excel cell contains set of sentences under 2 categories.

OK Points:
* ----------
* ----------
* ----------

Improvement Points:
* ----------
* ----------
* ----------

I need to copy the sentences under the heading improvement points. Enclosed trial excel sheet for further ref.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved