Sheet renaming and use as data

[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

 

[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

 

[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?

 

[Mike H]

Try,

=SUM(INDIRECT(A1 & "!" & "E19:G19"))

Drag down as required

Mike

 

[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

 

[Mike H]

Ah,

You wanted E19+G19 not E19:G19

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

Mike

 

[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

 

[Ernst - EXE Graphics]

Hi Pete.

Works like a charm.

Thanks

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

 

[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

 

[Ernst - EXE Graphics]

Hi mike

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

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

Thanks

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete