How do I even explain...?

ChrisSedgwick_

New Member
Joined
Feb 19, 2015
Messages
11
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,137
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,167
Members
410,903
Latest member
natesreich
Top