Sheet renaming and use as data
 

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

Sheet renaming and use as data
 

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" wrote:

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

Sheet renaming and use as data
 

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

Try,

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

Drag down as required

Mike

"Ernst - EXE Graphics" wrote:

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

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

On Jul 25, 11:19*am, Ernst - EXE Graphics
wrote:
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" wrote:
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" wrote:

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

- Show quoted text -

Sheet renaming and use as data
 

Ah,

You wanted E19+G19 not E19:G19

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

Mike

"Mike H" wrote:

Try,

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

Drag down as required

Mike

"Ernst - EXE Graphics" wrote:

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

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


"Mike H" wrote:

Try,

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

Drag down as required

Mike

"Ernst - EXE Graphics" wrote:

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

Hi Pete.

Works like a charm.

Thanks

"Pete_UK" wrote:

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

On Jul 25, 11:19 am, Ernst - EXE Graphics
wrote:
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" wrote:
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" wrote:

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

- Show quoted text -

Sheet renaming and use as data
 

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" wrote:

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


"Mike H" wrote:

Try,

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

Drag down as required

Mike

"Ernst - EXE Graphics" wrote:

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

Hi mike

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

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

Thanks



"Mike H" wrote:

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" wrote:

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


"Mike H" wrote:

Try,

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

Drag down as required

Mike

"Ernst - EXE Graphics" wrote:

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" wrote:

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" wrote:

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

Sheet renaming and use as data
 

You're welcome - thanks for feeding back.

Pete

On Jul 25, 12:05*pm, Ernst - EXE Graphics
wrote:
Hi Pete.

Works like a charm.

Thanks