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
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
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
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
Column C
=sheet2!g2
=sheet3!g2
=sheet4!g2
RE: Excel Links
RE: Excel Links
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
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
=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
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
RE: Excel Links
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"