Excel Links

[rnordquest]

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?

 

[onlyadrafter]

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!

 

[palusa]

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] [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

 

[onlyadrafter]

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!

 

[rnordquest]

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

 

[rnordquest]

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.

 

[onlyadrafter]

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!

 

[rnordquest]

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.

 

[QualityEngr]

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!

 

[rnordquest]

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.

 

[bltseattle]

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

 

[rnordquest]

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"