Start Free Trial

asked on

MS Excel 2010 Hyperlinking Between Tables

Hello!

I have a database (attached) that helps to create bid submittals. When the Add Work Item button is clicked a macro is run that allows for a new worksheet to be created and this new worksheet is based off the template. However, I am having some issues with the hyperlinking between the created worksheet and Table1 on the summary sheet. All of the prices are not connecting properly and are just copied from the previous row instead of connecting to the created worksheet. The prices should just be left blank until the user enters in the values in the worksheet that they are brought to when they created a new item.

Here are the following connections that must be made between Table1 and the created worksheet based off the template.

- column E connected to D38
- column F to F34
- column G to F35
- column H to F36
- column I to F40
- column J to D1

the previous code I was using for this was

WS.Cells(I, "E").Formula = "=" & sWItem & "!D38"
WS.Cells(I, "F").Formula = "=" & sWItem & "!F34"
WS.Cells(I, "G").Formula = "=" & sWItem & "!F35"
WS.Cells(I, "H").Formula = "=" & sWItem & "!F36"
WS.Cells(I, "I").Formula = "=" & sWItem & "!F40"
WS.Cells(I, "J").Formula = "=" & sWItem & "!D1"

But now the worksheet variables are defined differently and I cannot seem to switch them to work properly.

Thank you so much! Please let me know if I need to be more clear.
editing-EstimatingSpreadsheet.xlsm

what I see is

=HYPERLINK("[editing EstimatingSpreadsheet.xlsm]2!A1","2")

should be

=HYPERLINK("#'2'!"&ADDRESS(1,2),"2")

Open in new window

it goes to sheet 2, col2, row1. you can put any numbers there...

ASKER

Thanks for replying! I am a little confused though, what cell goes to sheet 2, col 2 row 1 with this formula? I hope that makes sense, like for example if I wanted column D from the summary sheet to link to B6 on the template sheet, how would I write that line of code?

ASKER

And actually, it is not really linking to B6 on the template, it is linking to B6 in the new worksheet that is created from the template.

=HYPERLINK("#'2'!"&ADDRESS(1,2),"2")

Open in new window

when you put this to any cell anywhere, it will show "2" in cell with a link to row 1 / col 2 on Sheet named "2"

ASKER

Okay! Thank you, the only thing is that I do not know what the name of the sheet will be because the user creates the name when they are prompted for the work item. Therefore, I somehow need to write into the Add Work Item macro what each column in the table is linked to in the created worksheet so that when the worksheet is created the cells are already linked. For example, each row in column D in table 1 will always be linked to B6 in the related worksheet for that row. I hope that makes sense!

you already have it!

sWItem

=HYPERLINK("#'" & sWItem & "'!" & ADDRESS(1,2), sWItem)

Open in new window

or

=HYPERLINK("#" & sWItem & "!" & ADDRESS(1,2), sWItem)

Open in new window

ASKER

Oh I see! Sorry I am very new to all of this, can I just write those lines of code into the macro?

ASKER

So linking column D in table 1 to B6 in the created worksheet I would write

=HYPERLINK("#'" & sWItem & "'!" & ADDRESS(6,2), sWItem)

Do you think that's correct? I am not sure how it knows to reference this to column D in the table!

Also thank you so much for your assistance it is greatly appreciated!

looks correct
but maybe you need some " left and right

=HYPERLINK("#'" & sWItem & "'!" & ADDRESS(6,2), """" & sWItem & """")

Open in new window

or this in your code

WS.Cells(I, "J").Formula = "=HYPERLINK(""#'" & sWItem & "'!"" & ADDRESS(1,4), """ & sWItem & """)"

Open in new window

ASKER

I just tried entering that into the macro and it says that I need to put something in front of the equals sign.. it said there is an expected line number, label, statement or end of statement

ASKER

Okay I just entered WS.Cells(I, "J").Formula = "=HYPERLINK(""#'" & sWItem & "'!"" & ADDRESS(1,4), """ & sWItem & """)" and I get the runtime error 424 object required. When I changed it to WSTemplate.Cells I get the runtime error 1004 application defined or object defined error

this code worked for me

    Dim H As String
    Dim sWItem As String
    
    sWItem = "Sheet2"
    H = "=HYPERLINK(""#'" & sWItem & "'!"" & ADDRESS(1,4), """ & sWItem & """)"
    
    Worksheets(1).Cells(1, "A").Formula = H

Open in new window

ASKER

Thank you so much! So in this case the H is the column you are referencing in the table? In that case, do I need to define each column as a string? And should I call it Sheet2 or do I need to write in "template" as that is actually the name of sheet 2?

just use

WS.Cells(I, "J").Formula = "=HYPERLINK(""#'" & sWItem & "'!"" & ADDRESS(1,4), """ & sWItem & """)"

Open in new window

it will add a link to WS.Cells(I, "J") that links to row 1 col 4 in Sheet named sWItem

ASKER

Okay! But do I need to define what WS is? Or is that just a normal variable? I am sorry I just want to try and understand how this work as I need to link many different cells.

ASKER

If I wanted to link column D in the summary table to B6 in the created worksheet would I just use:

Dim D As String
Dim sWItem As String

sWItem = "Sheet2"
H = "=HYPERLINK(""#'" & sWItem & "'!"" & ADDRESS(6,2), """ & sWItem & """)"

Worksheets(1).Cells(6, "B").Formula = D

? So sorry for all the questions.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Sorry no that code I was utilizing prior to changing the names of the worksheets and now there is new code.. I just posted it as an example. In the database I am using now WS is no longer defined. I attached the database I am using now and there is nothing about hyperlinking the table to the new created sheet in the AddWorkItem macro as I was not sure how to do that with the new code.
editing-EstimatingSpreadsheet.xlsm

ASKER

I am sorry I should have made that more clear!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

That code works to great to create a hyperlink in column C that takes you to the worksheet. But I am not sure how to rewrite it for all of the other links I need to make between the sheets. For example, for connecting column D in Table1 to B6 in the created worksheet based off the template. Or column F in Table1 to F34 in the created worksheet from the template.

Perhaps I am not even using the correct language... I actually do not think I am. It doesn't need to create a hyperlink.. I should have just used the word link. I just need the value of for example B6 in the created worksheet to link to the value in column D for that same work item. So if the user writes "Windows" in B6 of the worksheet named 2, column D in the work item row 2 would also say Windows. Sorry about this confusion, I didn't realize until now that this would not be a hyper link, just linking the two values.

So overall, I am just not sure what code to utilize to link each column in Table1 to a cell in the created worksheet based off the template.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I get a runtime error 1004 on that line of code that says application defined or object defined error.. Not sure why though.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

That would be great! But I know that the user wants to be able to write in the description once on the created worksheet.. And additionally, the costs must be linked to the summary sheet, which the user would not be able to enter in a prompting box as excel calculates the numbers on the worksheet. For example, F34 on the created worksheet should link to column F in Table1, which represents labour costs. All of the other cost columns are also linked to specific cells in the created worksheet based off the template.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

It's getting there!!! That works, but only for the most recent row. So if I add more than one work item, the first work item will have F33 and then the most recent will have F34. So it's perfect except for each row needs to be locked on F34, if that makes sense, instead of the first row being F32, and then F33 and then the most recent addition being F34. I tried changing the code to !$F$34" to attempt to lock it, but that just made every row have the same number as the most recent addition to the table. Any suggestions to allow each row to be locked to its specific worksheet's F34 cell?

Thank you again so much for continuing to help me! I really appreciate it!

it works, delete all rows and workitems and try again...
I added 3 items and I have

='7'!F34
='8'!F34
='9'!F34

ASKER

It is working now!! Thank you so much! Very much appreciated. I am not sure how difficult this would be to change now as the code is already complete, but do you think it would be possible to not have the work item number based on one above the previous work item number? Because the way it is now, I would have to give the spreadsheet to the user with a work item already entered in so that they could click the Add Work Item button and create a new work item. It would be great if the table could just be blank to start out with so that the user can just start by adding a work item, if that makes sense.

I could not get what you explain, but create an empty file, add one demo item, check all columns and formulas and distribute it...

ASKER

Okay! What I am saying is that it would be great if there did not need to be a demo item and that the table could just be blank when the user wants to add a new work item because right now when the table is blank and you try and use the Add Work Item code an error occurs because subscript is out of the range. Is it possible to alter the code so that it can be run even with the first row being blank?

I think that I need to alter this code below so that the old record isn't equal to the row size of the table.

Set OldRecord = Table.ListRows(RowSize)

WorkItem = OldRecord.Range(1, WorkItemColumnIndex) + 1
WorkItem = Val(InputBox("Please enter Work Item #:", "New Work Item", WorkItem))

ASKER

Thank you so much for your help with this question! It is so so appreciated. I still have another question about this database but as this thread is super long I am going to start a new question. Thanks again!!