Create a List of Absolute Reference in multiple worksheet

[Smokeyham]

I have a spreadsheet and would like to create a list of the value in an absolute reference from multiple worksheets. Here is what the formulas for the final result might look like:

='Management Analyst'!B2
='Human Resources'!B2

The spreadsheet is quite large (lots of worksheets), so the solution of just copying and pasting a reference is not practical.

Any suggestions as to a way which can automate this process?

Related Question
Do the worksheets need to have names which do NOT have spaces between words (i.e. "Management Analyst" should be one word).

 

[Jerry Sullivan]

Hi Gordon,

What do you mean when you use the phrase "an absolute reference"? Typically the term absolute reference distinguishes a range reference like: $A$1 which will still point to Cell A1 when copied to another cell, from a relative range reference like: A1.

In the context of your post, do you mean a formula which consists of only a direct reference to a single cell?

If that's correct, are you saying that you want to have a single worksheet that has one of these direct references to every non-empty cell in all the other sheets of the workbook?

 

[Smokeyham]

Hi Gordon,

What do you mean when you use the phrase "an absolute reference"? Typically the term absolute reference distinguishes a range reference like: $A$1 which will still point to Cell A1 when copied to another cell, from a relative range reference like: A1.

In the context of your post, do you mean a formula which consists of only a direct reference to a single cell?

If that's correct, are you saying that you want to have a single worksheet that has one of these direct references to every non-empty cell in all the other sheets of the workbook?

Hi Jerry,

Thanks for the response, and sorry for any confusion! What I have is the same cell (B2) which is in many work sheets. I would like to create a list of the values in each one of those cells, and show them in one worksheet.

 

[Jerry Sullivan]

Ok, a few more details and I'll suggest some VBA code.

1. Are you just applying this to Cell B2 of all your worksheets or does this need to be applied to multiple cells (eg All the cells in Column B of all worksheets)?

2. Do you want to have just the Values (constants) for each of those B2 cells or live formulas that are linked to those other sheets?

3. How should those resulting Values be displayed? (eg: in summary sheet B2: Sheets(1)!B2; C2: Sheets(2)!B2; D2: Sheets(3)!B2.....)

 

[Smokeyham]

Jerry,

Thanks for the help on this. Here are the answers:

1. Are you just applying this to Cell B2 of all your worksheets or does this need to be applied to multiple cells (eg All the cells in Column B of all worksheets)?
This will just be applied to cell B2 in each worksheet.

2. Do you want to have just the Values (constants) for each of those B2 cells or live formulas that are linked to those other sheets?
I just need the value which is shown in each of those cells. The value is created by a formula on each worksheet, but the formula does not need to be shown on the summary sheet.

3. How should those resulting Values be displayed? (eg: in summary sheet B2: Sheets(1)!B2; C2: Sheets(2)!B2; D2: Sheets(3)!B2.....)[/QUOTE]
Hopefully this answers the question... The final result would look something like this, where the numbers are the values shown on each worksheet:

Human Resources Technician 72
Management Analyst 65

Thanks again.

Gordon

 

[Jerry Sullivan]

Gordon,

Here's one way to code that....

Sub DisplayB2Values()
'--writes values of B2 for each worksheet in activeworkbook
'     to specified summary sheet.

 Dim wks As Worksheet, wksSummary As Worksheet
 Dim lNdx As Long, lWksCount As Long
 Dim vArray As Variant
 
 '--edit with name of sheet to display B2 values
 Set wksSummary = Sheets("Summary")
 lWksCount = Worksheets.Count
 If lWksCount < 2 Then Exit Sub
 ReDim vArray(1 To lWksCount - 1, 1 To 2)
 
 With wksSummary
   '--optional: clear previous values
   .Range("A:B").ClearContents
   
   '--write headers
   .Range("A1") = "Worksheet Name"
   .Range("B1") = "Value in B2"
   
 End With
 
 For Each wks In Worksheets
   '--gather data from each sheet except summary sheet
   If LCase(wks.Name) <> LCase(wksSummary.Name) Then
      lNdx = lNdx + 1
      vArray(lNdx, 1) = wks.Name
      vArray(lNdx, 2) = wks.Range("B2").Value
   End If
 
 Next wks
 
 wksSummary.Range("A2").Resize(lWksCount - 1, 2) = vArray
End Sub

 

[Smokeyham]

Jerry

Thanks so much! I appreciate your assistance on this.

Gordon

Gordon,

Here's one way to code that....

Sub DisplayB2Values()
'--writes values of B2 for each worksheet in activeworkbook
'     to specified summary sheet.

 Dim wks As Worksheet, wksSummary As Worksheet
 Dim lNdx As Long, lWksCount As Long
 Dim vArray As Variant
 
 '--edit with name of sheet to display B2 values
 Set wksSummary = Sheets("Summary")
 lWksCount = Worksheets.Count
 If lWksCount < 2 Then Exit Sub
 ReDim vArray(1 To lWksCount - 1, 1 To 2)
 
 With wksSummary
   '--optional: clear previous values
   .Range("A:B").ClearContents
   
   '--write headers
   .Range("A1") = "Worksheet Name"
   .Range("B1") = "Value in B2"
   
 End With
 
 For Each wks In Worksheets
   '--gather data from each sheet except summary sheet
   If LCase(wks.Name) <> LCase(wksSummary.Name) Then
      lNdx = lNdx + 1
      vArray(lNdx, 1) = wks.Name
      vArray(lNdx, 2) = wks.Range("B2").Value
   End If
 
 Next wks
 
 wksSummary.Range("A2").Resize(lWksCount - 1, 2) = vArray
End Sub