# Show sheet number or name based on a value



## tezza (Dec 17, 2022)

Hi all

I've been searching for a while now to try and do the following but have failed.

Can you put the following into something that Excel can do please?

Sheet 1 Cell I30 needs to show any sheet names/numbers that have the value of 1 in Cell S1 in sheets 3 to 12

EG: sheets 5 and 10 have the value of 1 in Cell S1 to Sheet 1 Cell I30 should show 5, 10


----------



## DanteAmor (Dec 17, 2022)

Try this:

Dante AmorAHI1229303, 4, 5, 6, 9, 12Sheet1Cell FormulasRangeFormulaI30I30=SUBSTITUTE(TRIM(
IF(Sheet3!S1=1,"3 ","") & IF(Sheet4!S1=1,"4 ","")& IF(Sheet5!S1=1,"5 ","")& IF(Sheet6!S1=1,"6 ","")&IF(Sheet7!S1=1,"7 ","") &
IF(Sheet8!S1=1,"8 ","")& IF(Sheet9!S1=1,"9 ","")& IF(Sheet10!S1=1,"10 ","")&IF(Sheet11!S1=1,"11 ","") & IF(Sheet12!S1=1,"12",""))," ", ", ")


----------



## offthelip (Dec 17, 2022)

you can do it with vBA very easily:

```
Sub test()
outp = ""
For i = 3 To Worksheets.Count
 nam = "Sheet" & i
 
 With Worksheets(i)
  If .Range(.Cells(1, 19), .Cells(1, 19)) = 1 Then
    outp = outp & i & ","
  End If
 End With
 Next i
 outp = Left(outp, Len(outp) - 1)
Worksheets("Sheet1").Range("I30") = outp
End Sub
```


----------



## tezza (Dec 17, 2022)

offthelip said:


> you can do it with vBA very easily:
> 
> ```
> Sub test()
> ...


Hi,

This code stops on myu machine at


```
outp = Left(outp, Len(outp) - 1)
```


----------



## DanteAmor (Dec 17, 2022)

If you want with macro, here is another option.
Just check that the names of the sheets must start with "Sheet" and followed by the number 3 to 12.
Result on sheet1


```
Sub showsheets()
  Dim i As Long
  Dim c As String
  For i = 3 To 12
    If Sheets("Sheet" & i).Range("S1").Value = 1 Then
      c = c & i & ", "
    End If
  Next
  If c <> "" Then Sheets("Sheet1").Range("I30").Value = Left(c, Len(c) - 2)
End Sub
```


----------



## tezza (Dec 17, 2022)

DanteAmor said:


> If you want with macro, here is another option.
> Just check that the names of the sheets must start with "Sheet" and followed by the number 3 to 12.
> Result on sheet1
> 
> ...


Perfect, thank you.


----------

