Sheet renaming and use as data

  • Thread starter Ernst - EXE Graphics
  • Start date
E

Ernst - EXE Graphics

I have a file with about 80 worksheets. How can I create a list in another
sheet that reflects all the sheet names
 
M

Mike H

Hi,

Right click the sheet tab of the 'other' sheet, View code and paste this in
and run it

Sub standard()
Dim x As Long
For x = 1 To Worksheets.Count
Cells(x, 1).Value = Sheets(x).Name
Next
End Sub

Mike
 
E

Ernst - EXE Graphics

Thanks Mike. That does the trick. Another question:

Now I have a list of names and I want to use the list with a formula to
calculate values from the sheet in the list:

sheet1 =+'sheet1'!E19+'sheet1'!G19
sheet2 =+'sheet2'!E19+'sheet2'!G19

How can I get the formula to get the sheet name from the list that was
created?
 
P

Pete_UK

Try this:

=INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19")

in B1 and then copy down.

An alternative is to amend the macro and do it in there.

Hope this helps.

Pete
 
M

Mike H

Ah,

You wanted E19+G19 not E19:G19

Try this instead
=INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19")

Mike
 
E

Ernst - EXE Graphics

Hi Mike

Do I use this formula as is?

Distell Winery R 28.00
SAB Miller R 358.36

The value of R28.00 is the formula and Distell Winery is cell "A3" from the
macro to list sheet names
 
E

Ernst - EXE Graphics

Hi Pete.

Works like a charm.

Thanks

Pete_UK said:
Try this:

=INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19")

in B1 and then copy down.

An alternative is to amend the macro and do it in there.

Hope this helps.

Pete
Click to expand...
 
M

Mike H

Ernst,

I'm a bit lost on this latest question. I can understand the sheet names
(Distell winery etc) in column A but where does R28.0 come from? You say it's
a formula but it looks more like a strange formula result to me.

Mike

I tink from the 2 examles I gave you that you can see how to refer to
another sheet using the sheet name f
 
E

Ernst - EXE Graphics

Hi mike

Problem is solved when the value of R28.00 is calculated with:

=INDIRECT("'"&A1&"'!E19")+INDIRECT("'"&A1&"'!G19")

Thanks
 
Top