Using cell text as a Sheetname in a formula

B

Brett W

Firstly, I have a large spreadsheet consisting of 1250 sheets.

I have one main sheet, listing all the sheetnames in column A.
Here's a small example of the first 4 rows in column A:
Cell A1: T014-06784
Cell A2: T014-06784G
Cell A3: T014-06785
Cell A4: T014-06785G

I want to use a formula in column B of my main sheet, that uses th
text from column A in the same row, and use that as the sheetname i
the formula. I also need to get the contents from cell F30 from eac
corresponding sheet.

I tried this in the first 4 rows of column B and it doesn't work
obviously.
Cell B1: ='A1'!F30
Cell B2: ='A2'!F30
Cell B3: ='A3'!F30
Cell B4: ='A4'!F30

I don't want to have to type the actual sheetname in each formula i
column B like this (but I want it to have the same effect):
Cell B1: ='T014-06784'!F30
Cell B2: ='T014-06784G'!F30
Cell B3: ='T014-06785'!F30
Cell B4: ='T014-06785G'!F30

Is there an easy way to do this so I that only need to make one formul
in say Cell B1, and then be able to copy the formula down for the othe
1249 cells ?

Cheer
 
S

SmilingPolitely

you could do something along the lines of:

Sub PopulateWorksheet()
Dim i, r
r = 1

'remove any existing data from columns A and B
Columns("A:B").ClearContents

'activate the destination worksheet
Worksheets("Main Sheet").Activate

'loop through all of the worksheets in the collection
For Each ws In Worksheets
If ws.Name <> "Main Sheet" Then
Cells(r, 1) = ws.Name
Cells(r, 2) = ws.Range("F30")
r = r + 1
End If
Next
End Sub
 
S

SmilingPolitely

mea cupa.... should have tested before posting.

Use this version.

Sub PopulateWorksheet()
Dim r, ws
r = 1

'activate the destination worksheet
Worksheets("Main Sheet").Activate

'remove any existing data from columns A and B
Columns("A:B").ClearContents

'loop through all of the worksheets in the collection
For Each ws In Worksheets
If ws.Name <> "Main Sheet" Then
Cells(r, 1) = ws.Name
Cells(r, 2) = ws.Range("F30")
r = r + 1
End If
Next
End Sub


serves me right for writing code on a newsgroup instead of in Excel!


sorry


you could do something along the lines of:

Sub PopulateWorksheet()
Dim i, r
r = 1

'remove any existing data from columns A and B
Columns("A:B").ClearContents

'activate the destination worksheet
Worksheets("Main Sheet").Activate

'loop through all of the worksheets in the collection
For Each ws In Worksheets
If ws.Name <> "Main Sheet" Then
Cells(r, 1) = ws.Name
Cells(r, 2) = ws.Range("F30")
r = r + 1
End If
Next
End Sub
Click to expand...
 
B

Brett W

Wow, some strange posts in this thread. :confused:

Anyway, if anyone could still help, I'd really appreciate it. I'v
tried the above with no luck.

The latest thing I've tried, is putting this formula in the B colum
cells of my Main Sheet.

="'"&A1&"'"&"!"&"F30"

but that doesn't really do the trick. That ends up putting this in th
cells.
E.G. Say I had this in cell A1: T014-06784

This is what I get in cell B1: 'T014-06784'!F30

What I end up with in cells in the B column, is what I want to use a
the formula, but obviously with the preceeding =

That being: ='T014-06784'!F30

Smiling Politely came up with a terrific macro :) , but it only put
the contents from F30 from the other 1650+ worksheets into the
column.

I need the formula in each cell of the B column, not just the content
from the other worksheets F30.

See, I then want to use this B column as a reference for other formula
on the other worksheets.

I don't believe using INDIRECT is a good idea.

Please help me.

Frank Kabel ? Anyone
 
D

Dave Peterson

I'm not Frank, but why isn't =indirect() good?

Although, I might change JMay's suggestion from:
=INDIRECT(A1&"!"&"B5")
to:
=INDIRECT("'" & A1 &"'!"&"F30")

just in case you have any "bad" worksheet names (embedded spaces, for example).
 
P

Peo Sjoblom

That's what happens when people have such bad imagination that they use
"Formula" as a subject line in a NG that is linked to
different forums
 
B

Brett W

Excellent!! Thanks Dave Peterson

=INDIRECT("'"&A1&"'!"&"F30")

That works perfectly!!



One extra thing though,
Is there a way to incorporate something with the formula shown belo
(which I have in F5) so that if the cells A5 & E5 are blank, it leave
the cell F5 blank also, instead of giving me a referencing error ?

=INDIRECT("'"&A5&"'!"&"F30")*E
 
B

BrianB

BRETT

I haven't read all the posts - many seem to bear no relationship t
your original query at all. I think you nearly got it :-

=INDIRECT("'" &A1&"'!F30"
 
D

Dave Peterson

one way is to just check and one way to check:
=if(and(a5="",e5=""),"",indirect("'"&A5&"'!"&"F30")*E5)
 
B

Brett W

Thankyou very very much for your help Dave Peterson. :))

Works like a charm!!

I now use this:

=if(and(a5="",e5=""),"",indirect("'"&A5&"'!F30")*E5)


Thanks also to BrianB :
 
Top