Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Link Tab Name To A Cell Without Using A Macro


Is there a way of embedding a formula in a worksheet so that the tab name of each worksheet is linked to a value in a cell on that sheet? (I don't want to run a macro)
In other words if the cell name is changed, the page name changes automatically.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro To Link Cell To Sheet & Cell Stated By User
I have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3

Dim Sheet As String
Sheet = InputBox("Take Data from where?")
Dim startcell As String
startcell = InputBox("Start Cell?")

ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""

View Replies!   View Related
Macro Create A Hyper Link Once Data Is Entered Into Specific Cell
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.

Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).

the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.

i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.

I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.

View Replies!   View Related
Can A Macro Contain A Link To A Website
I have two different worksheet that if data is changed then the user needs to go to the company's website to update information there as well.

Can a macro look at a range of cells and if a change is made then a message box would pop up and if the user said yes then they would be directed to the company's website.



View Replies!   View Related
Can I Link 3 Buttons To 1 Macro
Is it possible to link 3 buttons to one macro.

What I'm trying to do is have 3 buttons:

Load Button:
-Which makes a copy of the current sheet, renames it, and freezes its values

Print Button:
prints all the sheets

Clear button:
deletes all of the named sheets

I'm sure this can be done with an if then else statement, but I'm just not sure how to set it up to capture input.

View Replies!   View Related
Using Data From DDE Link In A Macro
I wonder if it is possible to bring data from DDE-links to variables in a macro without first bringing it to cells in Excel. The reason why I want to do this is because I only need the data when the macro is run (that is when a certain button is pushed). It would be a waste of system resources to continously link the data to cells in Excel. I asked the same question a few days ago at another forum but didn't get any reply.

View Replies!   View Related
Link By Formula Two Cells Within Macro
I have one list in one sheet that is source for the userform and combobox used on another sheet. With userform I make a choice and data is entered in one cell.
But is it possible to compare the choice that I make in combobox with the list on the first sheet and then make a simple link between these two cells, so that cell in the second sheet is always showing what is in the cell on the first sheet.
Reason for this is that items on the list on the first sheet sometimes change and I need to update the second sheet automatically.

Private Sub cmdOK_Click()

With Sheets("Presentation").Range("V18:V32")
Set c = . Find(cbochange.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveCell.Offset(0, 2).Formula = firstAddress
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Unload Me
End Sub

View Replies!   View Related
Macro To Link Check Boxes To Cells
I have a spreadsheet setup which includes a number of checkboxes set up using the forms toolbar.

I would like to link each of these checkboxes (in sheet1) to a cell reference in sheet2. I'm trying to write a macro that will do this for me to save me right clicking, choosing format control etc for each checkbox.

I found the code on this page: http://www.mrexcel.com/archive2/51300/59643.htm
which appears to be similar to what I am trying to achieve. In this case it creates the checkboxes in cells B3:B20 and links them to C3:C20.

I have already created all the checkboxes, and wish to use a macro to link them (for example) to C3:C20 in sheet2.


View Replies!   View Related
Link Variables & Sheets In Macro To A Userform
I have a sheet with 3 macros. It consists of two macros to produce results, and the third to bridge the two together where is all starts. I have built a userform in the sheet and am trying to get the variables in both sheets, to be user changeable and selectable via the userform.

In the attached data, the userform allows for the selection of two sheets, A and B. The user would select in A the sheet with data which is equivalent to Oval_An and in B the sheet with the data equivalent to Oval_DMA. The first macro, Find_75 runs, followed by the second macro, kTest, all working to produce results when hitting the Subtract button on the form. The Find_75 tolerance are also defined via the useform box, as well as kTest's compare tolerance.

View Replies!   View Related
Link Cells To Chosen Workbook Macro Code
I am trying to write a code in VBA to collect data from several weekly workbooks, and list them up in a monthly workbook.

These weekly workbooks change names depending on the week, for example, we have:

Report week 1.xls
Report week 2.xls
Report week 3.xls
Report week 4.xls

Each report has the same layout, so the data I want will be in the same cells. However, as the report names change as the weeks in the year go on, I can't write the code for weeks 1-4 as they will not work with week 5 onwards!

I have found out how to chose a file of my choice using:


Sub GetImportFileName()
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant

' Set up list of file filters
FInfo = "All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5 ......................


View Replies!   View Related
Macro To Link Large Number Of Cells Between Worksheets
I would like to write a macro that links cells in one workbook (working) to cells in another workbook (summary).

I don't want this to be a straight swap thought, I'd like it to contain the following formula:

=IF(Working!G8="a","a","")

The cells in the working workbook are listed vertically, while the cells in the summary workbook are listed horizontally.

ie. link G8-G18 in working to B3-K3 in summary
link H8-H18 in working to B4-K4 in summary
...etc

View Replies!   View Related
User Form Command Button Link To Macro Recoding
Is it possible to have a user form command button perform link or steps that are recorded with macro recorder? I am trying to reformat cut and paste a xls raw data file. I am trying to keep my file uploads to a min., but can upload an example if necessary.

View Replies!   View Related
Link The Numbers In The Form To Separate Parts Of A Large Printing Macro?
I have set up a form where the user can enter the number of copies they want for 10 items (I will call them item1, item2 etc for ease)

Is it possible to then link the numbers in the form to separate parts of a large printing Macro?

View Replies!   View Related
Link To Any Cell
I would like to ask if there's a way that i can create a link in a cell to another cell. Sample: in cell A1: there's a word "click to go to cell A150" in this case i dont have to use scroll down to go to cell A150.

View Replies!   View Related
Link To Another Cell
I would like to have a link in a merged set of cells that puts the insertion point to another cell. Scenario: In cell k30, the user puts in the amount of credit card debt a individual has, and scripts the user based on that information. Later on, they get a more accurate running total of the amount of debt and need to change the amount in k30 for an accurate quote. After ther running total, in cell j172(a merged box for instructions) i instruct the user to be sure to enter the new total in cell K30.

Questions:
1. Is there a way to put a button or a link that would put the insertion point back up to K30 automatically, but allow the user to change it themselves and not change it automatically?

View Replies!   View Related
Link A Cell To Another Cell On Same Worksheet Using A Formula
Is it possible to link a cell e.g. A10 that has a time value in it (there is a formula in this cell that puts in the time value) to another cell in a range of cells e.g. B1:B1000 that has the same time value?

I know someone out there knows how to do this.

View Replies!   View Related
Link Image To Cell
In Excel, I have a sheet for each team that I manage. Each sheet lists the team members and displays their picture below their names. Some people are on multiple teams/sheets.

I have a master list with everybody's name and picture that I copy and paste the pictures from when putting a team together. This has become a very tedious process.

Is there a way I can type the person's name on a team sheet and have their picture automatically appear below their name? I don't know if this can be done with some kind of lookup or if it will involve VB. I tried making the picture the background of an Autoshape or comment, but I could not figure out how to dynamically change the picture based on the name in a cell.

View Replies!   View Related
Possible To Link The Name Of A Tab To A Cell In Its Corresponding Worksheet
if it is possible to link the name of a tab to a cell in its corresponding worksheet.

View Replies!   View Related
Use A Cell Value In A Address Link...
I have is a summary worksheet with two columns. One column is for Project ID number and the other is an address link to a specific cell in the worksheet corresponding to the Project ID. Perhaps the best way to explain this is by giving an example:

Project ID (Column 1)
00-111

Balance (Column 2)
='C:Financial Sheets[00-111.xlsx]Phase_21'!$H$1

As you can see the spreadsheets are named after the Project ID number. What I would like to do, if possible, is instead of typing in the name of the spreadsheet for each project in column 2 (Balance), I could somehow reference the cell value in column 1 (Project ID). Something like this:

Project ID (Column 1)
00-111

Balance (Column 2)
='C:Financial Sheets["A2".xlsx]Phase_21'!$H$1..........

View Replies!   View Related
Link Data In 1 Cell
Sample data:

Machine Part Number machine 1 123 machine 2 123 machine 3 123 machine 1 123 machine 2 456 machine 1 456 machine 3 456

i need to based on tis sample data come out with sth like tt:

Used In: 123 Machine 1, Machine 2, Machine 3 456 Machine 2, Machine 1, Machine 3

I need to group those machines under 1 part number. For instance, part 123 is used in which machines, I need to state them in a way like above. But repeated should not be stated again.

View Replies!   View Related
Link Cell To Sheet Tab
Can you link a cell to a sheet tab. If yes how?

View Replies!   View Related
Lookup Cell Fed Via A DDE Link
I am trying to build a stock watchlist in excel 2007 with a dynamic link to a DDE server (paid for).There is no add-in or plug-in, I just CTL ALT & drag each code from a watchlist in the program I am using and place it in a cell, however can only choose one data field at a time. There are 14 data fields and over 150 codes in my list which makes 2100 cells. (My guess is about 3 days work)

I would like to just enter the stock code in say column A (A3) and with each DDE data field (e.g. lastprice, open, cose, high etc) entered in subsequent columns have it lookup the stock code in cell A3 and return the correct value based on the code in column A, rather than entering each cell individually.

Is it possible to write a macro or vba code to create the cell formula so I can just fill down and save myself 3 days work?

The DDE server I am using is formatted like this:

PROGRAMNAMElDATAFIELD!STOCKCODE

example

MISDATAlLASTPRICE!BHP.ASX

I thought I might be able to do something like the following, but it doesn't seem to work.

MISDATAlLASTPRICE!$A3

Note: The DDE server can only be accessed while the other program is running and is password protected

View Replies!   View Related
Link Cell Formats
Is there a way for your linked data to cary over formatting such as Bolds, borders, fonts and colors?

View Replies!   View Related
Find Which Cell Contain Link
I have a file contain some links with the other file. I want to remove these links but I don't know which cell contain the links. Is there anyway that can let me know which cell contains links so i needn't search one by one?

View Replies!   View Related
Spin Button Link To Another Cell
how to linked the "small change" value in a spin button to take the value of another cell?

View Replies!   View Related
Link Textbox To Cell/Range
How to set up textbox which value is in a spreadsheet and not the user input?

View Replies!   View Related
Link To A Cell From A Commandbar Control
I have a right click event for a cell where I create a custom commandbar with single or multiple control buttons based on the cell value. I want to link each control button to another cell in the same sheet.

View Replies!   View Related
Automatically Generate The Link Cell
A1, B1, C1, D1, E1, I1, J1 & D3 all linked to L1 thru S1 perspectively But when I copy the highted box and paste it, A4 automatically link to L4, as well as other fields. create a macro where excel automatically generate the link A1 = L1, A4 = L2, A7 = L3 and other fields perspectively all the way til the end?

View Replies!   View Related
Cell References Inside A LINK
is it possible inside this link to put a variable reference? for example to have a cell in my excel which in this case would be Report FY09 and if i change it to Repot FY08 the link would be c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4 or in another case if i have the name of the sheet like

='Report FY09'!D4
to do it
='Report FY08'!D4

or even the excel file
c:Report FY08[PPV REVENUES FY 09 - Actual.xls]PPV Actual'!$C$4:$BB$4
to do it
c:Report FY08[PPV REVENUES FY 08 - Actual.xls]PPV Actual'!$C$4:$BB$4

View Replies!   View Related
Read Data From Web By Using Link From Cell?
Iit is possible to do somethiling like this? I wanna load some data from web but I need link read from cell (e.g. sheet data!B1) .. so it means :

View Replies!   View Related
Link To Specific Cell In Preceeding Row!
This time I need to be able to reference a specific cell in the preceeding row of a GETPIVOTDATA formula. This is shortened example but it shows what I am trying to achieve:

A B C
NAME ID FRQ
Spellbound 1234 W
Spellbound 1234 W
Spellbound 1234 W

The GETPIVOTDATA formula is located in C6 in this example and needs to reference B5 i.e. the last ID cell reference. I need to work the formula this way as it will be placed at varying intervals in column C and the preceeding data for each NAME will vary as to how many rows each NAME contains.

View Replies!   View Related
Link Cell Contents Across Worksheets?
I work for an IT company who build their own PC's. And we use a simple Excel 2003 spreadsheet to piece-together and price-up PC's for retail, I've attached an example of the spreadsheet for you to see. It uses basic formulas so we can see at-a-glance things like VAT figures, margin, profit etc. Each PC we sell is contained within one Excel file, and each PC has it's own Workbook tab.

The problem I want to solve is whenever we change a component or price, we have to do each system manually, which gets very repetetive and time consuming. What I'd like to be able to do is change the price of, let's say, the motherboard, and have the price be reflected across each of the PC's using that same motherboard on each seperate tab. This same system would apply when we change a product name, product ID etc.

View Replies!   View Related
Link Content Of Textbox To A Cell
I have a Textbox in Sheet1 where there is some text or anything. I want to link this content to another textbox or cell in Sheet2.

How to do it? Is it possible to do without macros?

View Replies!   View Related
Link Changing As A Result Of A Value In Another Cell
In a worksheet I want that a link changes, after I change a value in another cell.

Example:
When cell A1 is 2006_04 the link should be:

C:/2006/2006_04/[...xls]Page1'!A1

When cell A1 is 2006_05 the link should be:

C:/2006/2006_05/[....xls]'!Page1!A1

View Replies!   View Related
Drop Down Box To Relatively Update Cell Link
I made drop down form controls that specify yes/no as to whether I have the documents I need for a project- when I selected yes the same row will turn grey. Each form control is set to move/resize with its cell, so it stays on top of the cell. I have the drop down form set to cell link to the cell below it in the form control settings. So it would be over cell A1 and the value of 0/1/2/ would be entered into A1. However whenever I copy the cell/row, the new form control drop down does not update its cell link by relative positioning, the new cell link is also A1 Ė thus both drop downs are linked together. I do NOT put absolute referencing $A$1 so I donít know why the new copied forms donít update and point to B1?

Why doesnít my drop down menu update its cell link position when I copy and paste the cell it is attached to? Can form controls update their cell links via relative references (that is without the $$)?

View Replies!   View Related
Link Cell To Textbox On UserForm
I am creating a user interface for inputing info into a sales order, and then printing the sales forms already pre-filled based on the user's input. SO, I need the user to be able to type text into a textbox that is in a multipage control that is in a userform, and then have it automatically populate a specific cell in my workbook. Seems like this should be relatively simple, but I am just getting started with vba and can't seem to figure this out.

View Replies!   View Related
Link Workbooks Using Cell Values
I need to link to a different workbook but using cell values.

For example, in the attached file in cell B4 it looks like this:

='C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls]Week 9'!$D$20

I need a way to change Week 9 in the above formula using a validation list.

I would also like to know how to specify the above workbook referenced using a cell as well, for instance, C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls] would be the contents of a cell or the ability to click on a cell and have it bring up an open dialog box to browse to the file and input it's path into that cell.

View Replies!   View Related
Link Userform Textbox To Cell
How do I refer to the contents of a userform's textbox on a worksheet?

The userform will be opened, edited, and closed by the user. I would like to be able to refer a cell on my sheet to that textbox so that they always have the same content.

View Replies!   View Related
Drop Down Cell Link Reference
I have 6 worksheets that are identical cosmetically. Each has the same drop down box list with the same name (dropdown1), however the cell link on each sheet is different. (ie. sheet1 dropdown1 cell link refers to a sheet ' lookup' cell $K$1, sheet2 dropdown1 cell link refers to sheet 'lookup' cell $K$2, etc.) I have the same command button in each sheet that performs the same task so I wanted to keep one module for all 6. Problem is I set a variable to the cell link in the code, and this has to be changed.

DropDownValue = Sheets("Lookup").Range("K1").Value

Is there any way I can set this variable using a property of the drop down list?

DropDownValue = dropdown1.celllink

I don't want to create 5 more duplicate modules just to change this one bit of code (I only use it this time in the code!). If there is no way to get the cell link value of the drop down, could I possibly put a worksheet change event on the lookup sheet to populate a certain static cell with the value of the last cell change?

View Replies!   View Related
Use Cell Value To Automatically Generate Link
For non-blank cells in the range C11:C65536, I want to have a hyperlink automatically generated (in the same cell), using the cell value as a relative variable in the link itself. One intended use for this is to populate the range with stock symbols, and have it generate hyperlinks to the following address:

http://news.moneycentral.msn.com/tic...asp?Symbol=XXX

Where XXX is the stock symbol in the given cell.

View Replies!   View Related
Dynamically Link Text Box To A Cell
In Excel have a customer list, and notes associated with each of them. On my form, where I perform a search (by customer name), I would like to link the Text Box to the cell with notes. How can It be done dynamically?

View Replies!   View Related
Update Link With File Name In Cell
I'm sure this is an easy one but it's unfortunately beyond me. I have a update links formula in which I would like for it to update with the name of the file being in a cell. Example:

The file name is Week20.xls

Cell A1 has the value of Week20.xls using a formula (="Week" & A2 & ".xls")

Currently I have it by looking at the actual file location like this: ='C:Documents and SettingsMy DocumentsWeekly Sales6 Sales Comparisons[Week20.xls]Daily Comparisons'!$I$4

But rather then pointing directly to the file path I want it to update by the name of the file in A1. So I'm sure its something as simple as replacing [Week20.xls] with some formula but I don't know what that one is.

View Replies!   View Related
Link Reference From A String In A Cell
I need to have a link to data in another file. The file will be in the same directory, however, the name of the file I need to link increments daily. I have a string being generated already with the name of the file I want to link to. I can easily add a cell reference to that string, but how do I use that string value in a link? Or - how do I use quick'n'easy VBA to snatch the required cell's data?

e.g.
Required data is in the file "production 060723.xls", in sheet "Daily Production", in cell "E2". Current file has a string being generated in a cell:
"\server1production dataproduction060723.xls"

The following day, the required data will be in a file called "production 060724.xls", same sheet, same cell. The current file's string data will also update to reflect the needed file. I know that similar questions have been asked here - but none that I saw in my search seemed quite what I need. Even if someone can direct me to the right thread, that would be great.

View Replies!   View Related
Link Cell To Picture Or Shape
It's difficult to explain, the attached shows the situation better. Basically there's a picture that is somehow linked to a cell. But, you can update the picture or the cell and the other one gets updated.

View Replies!   View Related
Hide Columns Based On A Cell Link
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


Private Sub 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

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.

View Replies!   View Related
Formula To Dicate Link Between Checkbox And Cell
I'm creating a spreadsheet which will have a grid of hundreds of check boxes (used to record progress on a project). In order to use the value of the check box in calculations, I'm linking each checkbox with another cell. So, if the checkbox is in cell F10, then i might link it with cell AF10, which is where the TRUE and FALSE indicators are.

If I copy the last row (which has checkboxes--let's call it row 99), and copy it below to expand the grid, the check boxes in the new row are still linked with row 99 cells (i.e., all the new check boxes in column F are still linked with AF99). So, presently I'm having to go through and manually re-link each pasted check box to a new cell.

Is there a way, using something along the lines of the INDIRECT function, where I can have a formula dictating what cell each checkbox is linked with, so that when I copy and paste a large number of these, they automatically point to the new cell (such that, F99's check box points to AF99, but if I copy this F99 check box into cell F115, then it would automatically be linked with cell AF115)?

View Replies!   View Related
Link Filename Of Workbook As Text In A Cell....
I have a workbook that gets saved many times with many different filenames.

All I want to do is have cell A1 display as text the name of the file.

eg similar to the date function, there should be a = filename function .

View Replies!   View Related
Copy Paste Cell & Link To Source
I would like to copy cells from one worksheet to another, but have the cells that I paste link to the original document. Is there an easy way to do this?

So if I'm pasting from worksheet A into worksheet B, I would like worksheet B to link to worksheet A.


View Replies!   View Related
Pivot Table Filter Link To Cell
how to change this code to select the choice "NACID" from cell "D1" instead of being hardcoded?

Sub Pivot1()
ActiveSheet.PivotTables("PivotTable4").PivotFields("Group").CurrentPage = _
"NACID"
End Sub


View Replies!   View Related
Link Spin Button To Active Cell
I've got a problem with the used spinbuttons. The code I used (see below)allows the user to choose a column (with the option button(A,B,C,D,E) in which changes would be made and than changes the value of the given cell according to the name of the spinbutton (which specifies the row). This forces me to use about 80 spinbuttons in a single sheet, and the whole workbook comprises of over 30 sheets pumping the whole file to a size of over 3 MB. To reduce the file size I need a code that would link a master spinbutton (one per sheet) to any active cell (the "just clicked one")

Private Sub sel(ByRef Column As String)
For Each obj In Application.ActiveSheet. OLEObjects
If obj.progID = "Forms.SpinButton.1" Then
obj.LinkedCell = Column & (Val(Mid$(obj.Name, 11)) + 3)
End If
Next obj
End Sub

Private Sub OptionButton1_Click()
sel "b"
End Sub

Private Sub OptionButton2_Click()
sel "c"
End Sub

View Replies!   View Related
Add Hyperlinks To Range & Link Based On Cell Value
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.

So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc

There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button

Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.

FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.

I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved