How do I even explain...?

[ChrisSedgwick_]

Hi there!

I'm really not sure where to start...

I'm making a basic Dashboard to allow me to track Production figures in our business. I've set the spreadsheet up and I'm now in the process of creating a 'Summary' sheet that will collate all of the figures.

The Dashboard is completed WEEKLY and includes 52 sheets.

On the Summary sheet, I have a simple table with the Week No's along the top and the KPI's down the left. Here comes the issue...

I'm obviously using a formula to collect the information from the individual sheet. However, I'm finding that I have to go into each specific formula and change the reference, to the correct sheet name. i.e

='1'!$C$8
='2'!$C$8
='3'!$C$8

The numbers are referencing the sheet name (obviously to state where the information is coming from). However to go into each cell and manually type in the sheet name take a hell of a lot of time, especially when there's a lot of KPI's per sheet. My question...

Is there a way, either by creating a table with the Sheet Names in, that I can get the formula to use the table to get the sheet name rather than type in manually?

You can access the spreadsheet in question by following the link below. Any help you can provide would be greatly appreciated.

https://www.dropbox.com/s/h7io981vxw0p4hp/Production Dashboard 2018.xlsx?dl=0

Thanks,
Chris.

 

[steve the fish]

Would you be better keeping the data in one sheet with each entry having an associated week number? Anyway you can do what you asked for but its slow and may impact the usability of your spreadsheet. This will produce whatever is in '1' cell c8:

=INDIRECT("'"&ROWS($A$1:A1)&"'!$C$8")

if copied down it produces '2' cell c8 etc.

 

[oldbrewer]

say you have a workbook with 5 sheets named in order apple orange pear lime lemon

in a macro the command sheets(4).select will open "lime"

the 4 can come from a formula of course

does that give you any ideas ?