×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Links

Excel Links

Excel Links

(OP)
I want a summary sheet to display the value of cell G2 from multiple sheets.  What I don't want to do is type in the links individually.  I have 300 sheet names e.g. 12345 xx yy.  The basic link is: ='12345 xx yy'!$g$2  How do I do this without typing in the individual sheet names?

RE: Excel Links

Hello,

I have assumed your Summary sheet is at the end

Try this code.

Sub cell_G2()
For sh = 1 To ThisWorkbook.Sheets.Count - 1
Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0).Value = Sheets(sh).Range("G2").Value
Next sh
End Sub

Change cell refs as required.

If the Summary Sheet is at the beginning, use this line instead.



For sh = 2 To ThisWorkbook.Sheets.Count

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Excel Links

rnordquest,

you should use the INDIRECT function.

SUppose yr sheets are called 12345 AA 01, 12345 BB 02, 12345 CC 03, etc, a possible solution in your summary sheet could be:

     [A]     [B]                       [C]                                      [D]
[1]  AA    01     =+"'12345 "&A1&" "&B1&"'!$G$2"     =INDIRECT(C1)
[2]  BB    02     =+"'12345 "&A2&" "&B2&"'!$G$2"     =INDIRECT(C2)
[3]  CC    03     =+"'12345 "&A3&" "&B3&"'!$G$2"     =INDIRECT(C3)


HIH.

_LF

RE: Excel Links

Hello again.

Having just re-read your request it sound as though you want the cell to be a formula e.g. =sheet1!G2.

So try this

Sub cell_G2()
For sh = 1 To ThisWorkbook.Sheets.Count - 1
Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0).Formula = "=" & Sheets(sh).Name & "!G2" 'Sheets(sh).Range("G2").Value
Next sh
End Sub


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Excel Links

(OP)
You're right about the formula.  I want to be able to copy it all the way down the summary sheet e.g.

Column C

=sheet2!g2
=sheet3!g2
=sheet4!g2

RE: Excel Links

(OP)
My problem is that when I type =sheet4!$G$2 into a cell I get a "File Not Found" window but if I type ="12345 XX YY"!$G$2 it works fine.

RE: Excel Links

Hello,

I have provided Visual Basic COde NOT a formula.

Select TOOLS --> MACRO --> VISUAL BASIC EDITOR

Select the project, and select INSERT --> MODULE

in the large window paste the code provided.

Back in the spreadsheet add a Command Button from the forms toolbar, onto the Summary Sheet, right click the mouse over the button, and select ASSIGN MACRO, highlight CEll_G2 and select. Now press the button on the spreasheet and voila, it should work!


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Excel Links

(OP)
I understood the VB and all that and I greatly appreciate your effort.  It will help a lot.

Now, why can't I directly type in a link like I did?  Why does the sheet name have to be used instead of the number?  Excel keeps track of both.

RE: Excel Links

rnordquest:  You do not need to get involved in VB programming to do what (I believe) you wanted.  Simply type in the formula:

=SUM(

then select all the worksheets with the G2 cell you want to sum
[your formual will show the range of worksheets.   You can easily select a range of worksheets by slecting the first worksheet, then select the last while holding down the shift key]

then type in:

$G$2)

Sounds complicated, but it is easy.  Try it, you'll like it!

RE: Excel Links

(OP)
QE. That's not what I want.  Cell G2 on each sheet is itself a Sum().  What I want to display on one sheet, are all the sums.

Column C in summary sheet

=sheet2!g2    !sum from sheet 2
=sheet3!g2    !sum from sheet 3
=sheet4!g2    !sum from sheet 4
etc.

My problem is this doesn't work.  Each sheet name is too long to type them all in.

RE: Excel Links

Did you try Palusa's method?  I used indirect function for this exact application, too, and no Visual Basic is needed.

RE: Excel Links

(OP)
bltseattle,

What I was trying to avoid by all this was typing in all 300 part numbers.  Palusa's method would require that when read literally.  So I modified what he did.

By using B8 I am able to avoid typing in all the part numbers but I had to add an extra ".  B8 and J8 were the locations of the part number and the rest of the worksheet name.  It works perfectly.

=+"'"&B8&" "&J8&"'!$G$2"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources