Dynamic INDIRECT value from another sheet

[tezza]

Hi,

I'm trying to grab data from another sheet but not getting the expect result.

The name of the sheet depends on the value in cell V1

=INDIRECT("'WK "&V1&"'!"&C2)

In this case, V1 = 29, then it should go to sheet 'WK 29'!C2 but I get a #REF error

The forumla help thinks I'm trying to read from WK 29 and the value in the current sheet at C2, (='WK 29'!45) which is wrong.

If I change it slightly to

=INDIRECT("'WK "&V1&"'!C2") then it works, however C2 become fixed, which won't drag down to D2, E2 etc.

Please help

 

[tezza]

What if you press F9?
Also, make sure that your value in cell V1 and your sheet name do not have any extra spaces or special characters in them.

It seems to work for me. Here is the sample data on my "WK 29" sheet:
View attachment 99304

And if I place the formula in cell C2 of my other sheet and drag down/across, this is what I see:
View attachment 99305

Ahh my mistake, I was testing it in Col R to see if it matches what's currently there. Yes it works great, thank you.

What's the best way to now add four sheets together, I initially was going with V1 + 1.... V1 + 2.... and V1 + 3 or would I have to set V to say 39, then W,X,Y to 40, 41, 42 and put a formula like below adding them all as seperate values:?

=OFFSET(INDIRECT("'WK "&$V$1&"'!C2"),ROW()-2,COLUMN()-3)+OFFSET(INDIRECT("'WK "&$W$1&"'!C2"),ROW()-2,COLUMN()-3)+OFFSET(INDIRECT("'WK "&$X$1&"'!C2"),ROW()-2,COLUMN()-3)+OFFSET(INDIRECT("'WK "&$Y$1&"'!C2"),ROW()-2,COLUMN()-3)

 

[Joe4]

Quite frankly, INDIRECT and OFFSET functions are not optimal. Besides this formula getting really long, both INDIRECT and OFFSET are volatile functions which can slow your workbook down (see: Excel Recalculation).

If it were me, I think I would rather use VBA code to create and apply the formula that is needed for each cell.
Not sure if that is something you would consider. If so, we would need to know the rules for what sheets/ranges to include and when to run it (if you want it to run automatically instead of running it manually).

 

[tezza]

Quite frankly, INDIRECT and OFFSET functions are not optimal. Besides this formula getting really long, both INDIRECT and OFFSET are volatile functions which can slow your workbook down (see: Excel Recalculation).

If it were me, I think I would rather use VBA code to create and apply the formula that is needed for each cell.
Not sure if that is something you would consider. If so, we would need to know the rules for what sheets/ranges to include and when to run it (if you want it to run automatically instead of running it manually).

I'll get back to you on that one and give you a better visual of what I'm doing.

 

[tezza]

What I have here is an example of what I'm using.

Sheets WK 29 to WK 32 look that same but have hours manually added into cells C2 - J down to bottom of list.

Inter Wks 29 - 32 looks for Name 1 and hours in WK 29 - WK 32 and sums the number of hours over the four weeks, and so on for all names.

Currently, it might not be pretty, but it works, however, every month the users have to manually change the formulas to add 4 weeks, eg:

=SUMIFS('WK 29'!C$2:C$96,'WK 29'!$A$2:$A$96,$A2)+SUMIFS('WK 30'!C$2:C$96,'WK 30'!$A$2:$A$96,$A2)+SUMIFS('WK 31'!C$2:C$96,'WK 31'!$A$2:$A$96,$A2)+SUMIFS('WK 32'!C$2:C$96,'WK 32'!$A$2:$A$96,$A2)

WK numbers change to

=SUMIFS('WK 33'!C$2:C$96,'WK 33'!$A$2:$A$96,$A2)+SUMIFS('WK 34'!C$2:C$96,'WK 34'!$A$2:$A$96,$A2)+SUMIFS('WK 35'!C$2:C$96,'WK 35'!$A$2:$A$96,$A2)+SUMIFS('WK 36'!C$2:C$96,'WK 36'!$A$2:$A$96,$A2)

C$96 is just a random row I added to allow for addional names to be added but I know it can be done better.

Not many staff are excel savvy so I end up having to update multiple departments every month.

How can I make the 'InterWks 29 - 32' automatically look for sheets 'WK 29 - WK 32' ?

I'm open to options here.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Area	ASP Weeks 29 - 32	Normal	Weekend	Office	Sp Task	Annual Leave	Sick	St Miles	On Call
2	7262	Name 1	0	0	9	0	0	0	0	0
3	17934	Name 2	26	6	0	0	0	0	0	0
4	17939	Name 3	0	0	0	0	0	0	0	0
5	17942	Name 4	9	9	0	0	0	0	0	0
6	17965	Name 5	0	0	4	0	0	0	0	0
7	17967	Name 6	0	0	16	0	0	0	0	0
8	18033	Name 7	13	0	0	0	0	0	0	0
9	18139	Name 8	0	0	12	0	0	0	0	0
10	20038	Name 9	13	9	0	0	0	0	0	0
11	21483	Name 10	5	0	0	0	0	0	0	0
12	22349	Name 11	14	0	0	0	0	0	0	0
13
14
15
Inter Wks 29 - 32

Cell Formulas
Range	Formula
C2:C12	C2	=SUMIFS('WK 29'!C$2:C$96,'WK 29'!$A$2:$A$96,$A2)+SUMIFS('WK 30'!C$2:C$96,'WK 30'!$A$2:$A$96,$A2)+SUMIFS('WK 31'!C$2:C$96,'WK 31'!$A$2:$A$96,$A2)+SUMIFS('WK 32'!C$2:C$96,'WK 32'!$A$2:$A$96,$A2)+D2
D2:J12	D2	=SUMIFS('WK 29'!D$2:D$96,'WK 29'!$A$2:$A$96,$A2)+SUMIFS('WK 30'!D$2:D$96,'WK 30'!$A$2:$A$96,$A2)+SUMIFS('WK 31'!D$2:D$96,'WK 31'!$A$2:$A$96,$A2)+SUMIFS('WK 32'!D$2:D$96,'WK 32'!$A$2:$A$96,$A2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:J15,L2:L15	Cell Value	=0	text	NO

 

[Joe4]

If it were me, I would probably create an Access Database to do this. What you really have is a "relational database" (a bunch of related data), and Access (or SQL) handle it SO much better than Excel, as they are relational database programs by nature, and Excel is not. But of course you need to understand relational database theory and Access in order to design the database properly.

Otherwise, I would recommend changing the structure of your data to make it easier to work with, i.e. have one large data sheet where ALL the data gets entered, like this:

And then you can easily write SUMIF formulas to include totals for just the week numbers you want (i.e. >=29 and <=32).

 

[tezza]

If it were me, I would probably create an Access Database to do this. What you really have is a "relational database" (a bunch of related data), and Access (or SQL) handle it SO much better than Excel, as they are relational database programs by nature, and Excel is not. But of course you need to understand relational database theory and Access in order to design the database properly.

Otherwise, I would recommend changing the structure of your data to make it easier to work with, i.e. have one large data sheet where ALL the data gets entered, like this:
View attachment 99456

And then you can easily write SUMIF formulas to include totals for just the week numbers you want (i.e. >=29 and <=32).

Interesting approach. I'll look more into this and see if it's a viable option for the smaller groups.

The main sheet would likely have in excess of 1000+ rows per week.

The data is generated as a csv file per week, which then runs a explicit macro from another workbook to make it like above before entering any hours.