Using cell text as a Sheetname in a formula

  • Thread starter Thread starter Brett W
  • Start date Start date
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
 
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
 
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...
 
Thankyou very much so far, but unfortunately it's not exactly doing wha
I'm after.

I'll try to explain a little more.
I should mention that I work for a manufacturing company.

The entire spreadsheet is to work out the current costs of 1250
Inventory Items, each having their own Bill of Materials.
Each sheetname represents an Invetory Item Code.
Each sheet consist of a Bill of Materials for each Inventory Item.

Rows 5 to 24 on each worksheet (except the Main Sheet), consist o
components used in that Bill of Materials.
(Note: Components are other Inventory Items)
There may be between 1 and 20 components used in each Bill o
Materials.
So components (Inventory Codes) go in A5-A24, Descriptions in B5-B24
C5-C24 & D5-D24 are left blank, Quantities in E5-E24, and Costs in F
to F24.

The cell F25 on each worksheet (except the Main Sheet), is the Sum o
F5 to F24.
Row 26 on each worksheet (except the Main Sheet) is left blank.

The cells F27 to F29 consist of additional costs for Labour, Overheads
and Galvanizing.

(These values are manually entered as each Invetory Item is different)

The cell F30 on each worksheet (except the Main Sheet), is the Sum o
F25 to F29, which is the total cost for that Inventory Item.


I have one extra worksheet called ARM (stands for Assorted Ra
Materials)
This sheet consists of only 1 row like this:
Cell A1: ARM, Cell B1: Assorted Raw Material, Cell C1: 0.01, Cell D1
100%, E1: left blank, F1: =C1*D1

There are several "base level" Bill of Materials which contain ARM a
the only component.
"Base level" Bill of Materials consist of only ARM as the onl
component used.
I'll use an example as I explain here that if a Bill of Material
requires $8.57 worth of ARM

(Assorted Raw Materials), I would go to the worksheet e.g. T023-0043
and put this on Row 5:
Cell A5: ARM, Cell B5: Assorted Raw Materials, Cell E5: 857, Cell F5
=ARM!F1*E5
Therefore Cell F25 would total to $8.57
I would also manually enter additional costs for Labour, say $1.20
Overheads, say $0.97.
Therefore Cell F30 would be a grand total of $10.74

The next worksheet (e.g. let's call it T023-00576G) may be a "secon
level" Bill of Materials which may use e.g. 5 inventory items, one o
them being e.g. T023-00438 as one of the components.


OK. This is the bit I need, mentioned in my previous post. (if needed
please read my previous post before continuing)

My main sheet wants to consist of all the Inventory Items Codes i
column A (which are all the other worksheet names, which I alread
have) and a formula for each in column B.
Here's an example of what I wanted on my main sheet: Cell A27
T023-00438, Cell B27: ='T023-00438'!F30

I would then go to worksheet T023-00576G, and in column F for th
component T023-00438 I would put the formula =Main Sheet!B27*E5

I would enter the same type of formula (to obtain the cost) for eac
component in each and every worksheet.



If there is an easier way to do all this, like if there is a way t
lookup each component in every worksheet, then go and find thei
corresponding grand total cost (located at F30 on the worksheets), the
get it to enter it in column F (on the same row as the components)
then that would be just terrific.
The only thing that it must do, is work on base level Bill of Material
first, then "2nd level" Bill of Materials", then 3rd level, and so on
and so on.
Several Inventory Items have anywhere up to 15 levels of Bill o
Materials.

This spreadsheet is quite a mammoth undertaking.

I hope you understand all of the above, and I appreciate you taking th
time to read it.

Many thanks in advance
 
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
 
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).
 
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
 
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
 
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"
 
one way is to just check and one way to check:
=if(and(a5="",e5=""),"",indirect("'"&A5&"'!"&"F30")*E5)
 
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 :
 
Back
Top