Macro to reference to a cell in indiv sheets

D

dolphinv4

Hi,

I have a column A that lists the name of all the sheets
in my workbook. I'd like to have column B equals to cell
C10 of the corresponding sheets in column A.

I already have a macro to list all the sheets in my
workbook so I'd like this macro to update column B at the
same time I run the macro.

Thanks!
Val
 
J

JE McGimpsey

One way:

Assuming your worksheet list starts in A2:

You could do this without macros by entering:

B2: =IF(A2<>"",INDIRECT("'" & A2 & "'!C10),"")

and copying down as far as necessary.

If your concern is having exactly the right number of formulae, you
could use something like this:

Public Sub UpdateC10()
Range("B2:B1000").ClearContents
With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
.Formula = "=INDIRECT(""'"" & A2 & ""'!C10"")"
End With
End Sub
 
D

dolphinv4

Hi,

I tried your formula but it doesn't work. It shows a
#REF. Was there a typo error in the formula other than a
missing close bracket? I've never used a INDIRECT before
so don't know how to amend.

Thanks,
Val
 
J

JE McGimpsey

The close bracket wasn't missing, but I inadvertently dropped a quote.
Try:

=IF(A2<>"",INDIRECT("'"&A2&"'!C10"),"")
 
J

Jim May

JE:
I've entered in Cell C5 =IF(A5<>"",INDIRECT("'"&A5&"'!E5"),"") '<< E5
hardcoded
In cell B5 I have entered E5
How can I reflect the B5 reference in the above?
TIA,
 
Top