Link By Formula Two Cells Within Macro

Jan 9, 2008

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 3 Replies


ADVERTISEMENT

Macro To Link Check Boxes To Cells

Feb 8, 2008

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 9 Replies View Related

Macro To Link Large Number Of Cells Between Worksheets

Feb 18, 2008

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 9 Replies View Related

Link Cells To Chosen Workbook Macro Code

May 19, 2008

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 9 Replies View Related

Formula That Uses Link In A Cell To Create Offset Link

Jun 28, 2013

I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.

On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.

In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?

View 6 Replies View Related

Link Formula / Update Formula Automatically

Jul 31, 2006

I’m working on quite huge spreadsheets. The same formula will be used on more than 30 sheets.

So far if I have to change the formula in one cell I have to go to each sheet and change it manually (which is quite boring and time consuming).

I’m wondering if it’s possible to reference all the formulas on the sheet to one sheet (which some kind of a master sheet with all the formulas).

I can copy the formula with the “Past Special” function but the function is not updating if I change the formula on the main sheet.

So is it possible to link it and that it’ll be updated automatically when I’m changing the formula on the main sheet?

View 9 Replies View Related

Linking Cells By Looking Up Part Of Link In Other Cells

Dec 2, 2008

I'm wondering if there is a way of linking to cells by looking up part of the link in another cell. E.g.In Sheet1, cell A1, i have some data that I want link to from Sheet2. Normally I'd do that by using "=Sheet1!A1" in the cell in Sheet2.

But I'd like to lookup the "A1" part of the link from two different cells in Sheet 2. Something like: =Sheet1!(Text from B1 in Sheet2 AND Text from A2 in Sheet2)
or: =Sheet1!(Sheet2!B1&Sheet2!A2). So that if Sheet2!B1 would contain an "A", and Sheet2!A2 would contain a "1" the resulting link would be the correct "=Sheet1!A1"

View 4 Replies View Related

Link Formula To Next Row Up With A Value Present

Feb 4, 2014

I've created a spreadsheet that I use to calculate weights of steel members for my job for estimate purposes. I'm trying to figure out how to save some time by not having to enter/copy/paste formulas every time I do takeoff for an estimate.

The bold green cells in column K are always linked to the yellow "Part Heading" above in Column A. As I do my estimates, I copy and paste a "Part Heading" with a "PL/Member" formula below for the next piece I'm working on. Every time I paste a new "Part Heading" with a "PL/Member" formula, I must change the formula in Column K to link to the next cell up in column A with a value present. In the screen shot, cell K/90-K/96 are linked to cell A/89. Cell K/83-K/87 are dependent cell A/82; K/74-K/80 are dependent on cell A/73, and so forth. Since each "Part" varies on total number of "Members", I can't seem to write a formula that will stick when copying and pasting.

My question is: Is there a way that I can automatically link each cell in Column K to the next cell up in Column A with a value present?

View 7 Replies View Related

Removing One Link Out Of Many In Formula

Jun 23, 2009

Within my spreadsheet all the formulae are sums of cell references from 7 other documents. I now don't need one of the documents and therefore want to remove just that one link only from the formula. I would do it manually but I have hundreds to change! I have tried breaking the link to that one document but that removes the entire formula.

View 2 Replies View Related

Using Link Formula And Concatenation

Apr 21, 2012

I have a formula in the target workbook in cell C3 =-'data.xls!'np2011'

However, when UI copy the formula to D3 , the formula remains as =-'data.xls!'np2011'

2011 after np is the year. The names in the source workbook have been named np2011, np2010, np2009 etc

np2011 is a range name in the source workbook. When I copy the formula to d3, it should change to -'data.xls!'np2010'

In the target workbook I have the years in cells C1 to L1. How can I get the formula to change when copying/ Alternatively can one concatenate the range name to C1 for eg such as ="-'data.xls!'np&c1&"' I have tried to do this, but cannot get it to work.

View 5 Replies View Related

Formula Link Across Spreadsheets

Jun 12, 2006

I created a summary page for some reports I do and I'm having trouble keeping them linked together as my original data has rows deleted and inserted. There are 30 spreadsheets worth of information and I have them linked to a summary file. Each month I insert a new tab to each spreadsheet. I set up all of the formulas last month but see a problem this month when I copy and past the formulas to the new tab. On the 30 spreadsheets my data moves up and down so the row referenced in the summary formula is wrong. I am looking at having to type "=, click on original spreadsheet, select cell, enter, repeat" all over again. I pull 5 numbers from each sheet, and have multiple formulas to double check the math so I would be repeating this process over 300 times.

After all that, my question is...Is there a formula I can use that will pull this for me without needing to be edited each month. One idea I thought would work is to tell the formula to equal the first cell in Column B that is in red text and I could just change the font color on the orginial spreadsheet. But, I don't know how to make a formula say that or if excel can even do that.

View 9 Replies View Related

How To Link Up Cells From One To Another

Jun 20, 2011

Here it is

1. I want to put the AMOUNT on E6
2. from there on F6 put the account number on there (FOR)
3. Now once the account number is put on F6 it would link up the same account number on (H6-H32)
4. then when its linked up from those cells put the amount where you typed it up from to move to that account to THIS MONTH (J6-J32)

so from E6 to F6 it would link up, put an amount of 100.00 on E6 and an account number on F6.

Once account number is typed up it would link up to the account number to the other side to H6 -H32

Finally the amount that you typed up from E6 would link up to the account number and pop up to J6 - J32 (THIS MONTH) and if you put on other spaces on the cells it would add up different amounts and put account number and it would link up and the amount would move to THIS MONTH.

View 4 Replies View Related

Formula Contains A Link To Closed Workbook?

Jan 5, 2014

How do I get rid of the link??? My formula seems like it is not working because it has a link which I cannot find anywhere.

dog.xlsx

Screen shot 2014-01-05 at 2.38.08 AM.jpg

View 6 Replies View Related

Changing Section Of Link In Formula?

Jan 20, 2014

It's a new year, which means new tables and lists. I have a list of 450 People with a reference to their own individual file. They all receive a new file at the start of the new year that I Need to be able to reference in a new list. Here's an example

Joe Bloggs has a cell next to his Name referencing

[Code] .....

In 2014 Joe will have 'Bloggs 2014.xls' as will 449 of his friends, each named individually. The only part that changes from 2013 to 2014 is the date in the Name of the file. Can I somehow just Change that one part of the link without having to manually relink everything?

View 4 Replies View Related

Unable To Link In Certain Cells

Feb 1, 2008

I have inherited a workbook and wish to link certain cells from various worksheets.On one work sheet I am unable to selct any cell (cursor does not change) to link in and on another sheet there are a just a range of cells I cannot access - everywhere else works normally. There are no macros and the workbook is not protected.

View 13 Replies View Related

Link Cells In Different Sheet

Dec 6, 2008

I have a workbook that I am trying to get easier to work with, I have sheet 1 that my product codes,cost,mark up % and amount sold etc. and I have sheet 2 that has suppliers order No's, Cust name/email etc. and the At the moment in my example I manually hyperlink AE2 in sheet 1 to A2 in sheet 2 - and I manually copy cells P2 & Q2 in sheet 1 and paste link into E2 & F3 sheet 2.

I want cells E2 & F3 in sheet 2 to automatically link to their corresponding cells in sheet 1 P2 & Q2

View 10 Replies View Related

Link Images To Cells

Apr 13, 2009

My goal is probably very simple, but I knowledge is not enough to achieve that.

What I need is to have those two tables to interact with each other. One is a numeric table and the other an images table. Each number is linked to an image, somewhere on my HD, that should display automatically on the table on the right. I made a draft of the two tables, so you can see exactly what I want. All images, about 50, are the same size 128x128 px.
I must be able to change those numbers on the left table, having the images on the right table reflecting those changes. How can I achieve that goal? Can someone explain me that on detail, or is asking too much? I'm not very good at excel, just a regular user.

Here is the draft sheet.

Microsoft Excel - Images_Table.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB10=BCDEFGHIJKLMNOP21234567 Pic1Pic2Pic3Pic4Pic5Pic6Pic7311121314151617 Pic11Pic12Pic13Pic14Pic15Pic16Pic17421222324252627 Pic21Pic22Pic23Pic24Pic25Pic26Pic27531323334353637 Pic31Pic32Pic33Pic34Pic35Pic36Pic37641424344454647 Pic41Pic42Pic43Pic44Pic45Pic46Pic47751525354555657 Pic51Pic52Pic53Pic54Pic55Pic56Pic57861626364656667 Pic61Pic62Pic63Pic64Pic65Pic66Pic67Folha1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Using Data From DDE Link In A Macro

Nov 4, 2008

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 2 Replies View Related

Create Macro For DDE Link

Apr 5, 2006

I am using an excel add-in to create a DDE link between a market data provider and my spreadsheet. ( Add-in is called SigTools.xla )

DDE link works fine and target cells are updated dynamically as market data changes so my excel sheet values are changing constantly.

The formula =WinRos|LAST!GOOG will update target cell with most recent market prtice for Google.

Since this formula is not an excel formula I can't reference excel cells with formula - ie =WinRos|LAST!Indirect&A3 or anything like this won't work.

I thought a Macro might work to simulate "typing in correct formula" and I created following Macro which I thought would work but unfortunately it does not.

Sub Macro2()
'
Dim strSymb As String
strSymb = Cells(4, 1).Value
Application.Goto Reference:="TargetCell"
ActiveCell.FormulaR1C1 = "=WinRos|LAST! & strSymb & "
End Sub

The idea was that Macro would behave just as if I typed in =WinRos|LAST! & strSymb & and substituted the symbol name contained in Cells(4, 1) - whatever that happenned to be.

Seems reasonable but I get an error "Application-defined or object-defined error"

Alternatively I could re-type the formula each time but it would be much better to somehow reference a specific symbol in a particular cell via functioning Macro.

View 2 Replies View Related

Can I Link 3 Buttons To 1 Macro

May 11, 2007

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 9 Replies View Related

Can A Macro Contain A Link To A Website

Jul 12, 2009

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 9 Replies View Related

Link Tab Name To A Cell Without Using A Macro

Aug 17, 2009

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 9 Replies View Related

Link Formula To Unopened Excel Workbook

Oct 14, 2012

Im looking to insert a formula in vba code that inserts the formula from a closed workbook to the active cell. At the moment i have this as an example of what i need:

VB:
ActiveCell.FormulaR1C1 = "='[SRS 5FFBEP.xlsm]7%'!RC"

However the file part in square brackets [SRS 5FFBEP.xlsm] and tab 7% i would like to replace with a link to a cell reference that contains the file path as this needs to change and update so i would end up something like this:

VB:
ActiveCell.FormulaR1C1 = "=('" & Workbooks(Worksheets("SRS Files").Range("d29")).Worksheets("SRS Import").Range("c10") & "'!RC)

Note that cell Worksheets("SRS Files").Range("d29") contains the full file path (in text format) and Worksheets("SRS Import").Range("c10") is the link to the tab that reads the 7% (in text format).

As it stands the first set of code works fine however i need it to be able to change as previously stated. So what i need is to replace the file name and tab with cell references

View 1 Replies View Related

Must Open External File To Link Formula

Oct 26, 2009

We're using SUMIF and it won't work unless the external excel file is open.

This is the formula we're using:

View 7 Replies View Related

Link/copy Value/formula To New Copied Worksheet

Jul 3, 2007

I have a worksheet. In sheet3 The value of column L2 is the value of column M2 in sheet . Everytime I copy the new sheet, I have to re type the formula for this new sheet. Is there a way to give formual such that when I copy new worksheet the formula will come automatically as it always has to link from the M2 column of the previous sheet?

View 9 Replies View Related

Formula To Link Matching Info On Another Page

Dec 31, 2008

I have a Date in Cell A1 Page 1 and I need a formula to put in Cell A2 on Page 1 that if there is a "matching" Date in any cell in Row B1-B7 of Page 2 that the data in the corresponding row C1-C7 on Page 2 will be placed back on Page 1 Cell A2.

View 9 Replies View Related

Formula To Dicate Link Between Checkbox And Cell

Jan 8, 2010

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 9 Replies View Related

Link Cell Comments To Formula Result

Jul 24, 2006

Can cell comments be returned together with the results of Index / Match function. If not is there any way to link the coments ?

View 3 Replies View Related

Link Formula: Use Cell Data As Workbook Name

Oct 24, 2006

If I have a two files called test1.xls and test2.xls for example. Is it possible to link test1.xls to test2.xls by putting the filename in a cell?

i.e. In test2 put the filename in say A1 and in the link put the path and instead of naming the file you want to link to put in A1 instead.

I know it doesn't work if you do it that way I was just wondering if there was a way to do it.

View 5 Replies View Related

Extract Hyperlink Formula Link Address

Nov 17, 2006

Not sure if anybody has been succesful or not in the following but I am writing a macro to check that each hyperlink in my report is operational. My situation is as follows:

Cell A1 has the following hyperlink formula: =hyperlink(Worksheet1!B1,Worksheet1!B2)

I am attempting a workaround to capture the cell reference (B1) where the path and filename is stored and directing the macro to open the link. But when I try to send the hyperlink formula (Cell A1) to a string variable in vba, the result is the contents of Worksheet1!B2 instead of the entire formula.

View 2 Replies View Related







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