Using Sheet references in formula/VBA

[mickyh]

Good Morning.

I would like some help, please.
I have a workbook where amongst other things I have a sheet that collates information from up to 25 other sheets. It's a simple formula but it reoccurs about 500 times and whenever I make a change I have to rewrite the formula for each sheet. Is it possible to use a cell reference instead of the sheet name? either by formula or VBA?
The sheets are named 1 - 25 so, for example, on the sheet that collates the information the formula in cell B4 is as follows

=IF('1'!C8>=1,1,"")

and the formula in H82 is

=IF('1'!I91>=1,1,"")

with most of the cells in between following the same pattern. sheet 2 starts in cell J4, Sheet 3 in R4, and so on.
I'd like to replace the sheet name with a cell reference that contains the cell name if that is possible. I'd like to be able to use B1 to contain the name of sheet 1, J1 to contain the name of Sheet 2, and so on so that referring to those cells replaces the sheet name in my formula.
This might not even be possible so thanks for looking, if you have any suggestions then they will be gratefully received.

 

[Dan_W]

I thought I might have a solution, but then I read this:

sheet 2 starts in cell J4, Sheet 3 in R4, and so on.

and realised it's not as straight forward as I had first thought. Do you think you could please show what you're after using the XL2BB addin? XL2BB - Excel Range to BBCode
It would help a lot if I can visualise what you're hoping to accomplish.

 

[Dan_W]

Actually, without yet knowing exactly what it is that you're after, I wonder if the INDIRECT function might be useful.

ScrollText_Scores_Test (version 1).xlsb
	A	B	C	D	E	F	G
1	Sheet Name	Cell Reference	Cell Value
2	1	E4	10
3	2	F4	20		Sample Data Set
4	3	G4	30		10	20	30
1

Cell Formulas
Range	Formula
C2:C4	C2	=INDIRECT("'"&A2&"'!"&B2)

Basically, I created three worksheets - entitled "1'", "2", and "3". In cells E4 to G4 on all three sheets, I put the same data set: 10, 20, and 30.
Using the INDIRECT formula in Column C, you can access the value on the designated sheet (see Column A) and designated cell (see Column B).
Would that help?

 

[mickyh]

Hi Dan
Thanks for getting back to me so quickly, I was having trouble with xl2bb but I think I have sorted it now. (I have replicated the page on a separate sheet)

Sample weekly allocation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		W/C 12/Sep								W/C 12/Sep								W/C 12/Sep
2	Current Jobs	Mick								Employee 2								Employee 3
3	Construction	M	T	W	T	F	S	S		M	T	W	T	F	S	S		M	T	W	T	F	S	S
4	5889 St Lukes Doors
5	6687 Bowerhill Rooflights Phase 2
6	6694 Devizes School MSH
7	Soverign Kitchens and Bathrooms.
8	6777 18 Elm Park
9	6750 21 Glebe Road
10	5933 Tanglin Farm Air BnB
11
12	6718 LPA Fencing
13	6729 9 Thornhill
14	6773 Watermoor Road
15
16	Snagging Projects
17	6661 The Ridge
18	6685 52 Furze Close
19	6729 Thornhill Close
20
21	5994 5 Shelly Avenue
22	6552 Spratts Barn Cresent
23	6694 Devizes School MSH
24	Instadoor
25	Maintenance/Minor Works
26	Office/Admin
27	Training/College
28	Holiday/Unpaid Leave/Sick Leave
Matrix

Cell Formulas
Range	Formula
A4:A28	A4	='1'!A8
B4:H28,R4:X28,J4:P28	B4	=IF('1'!C8>0,1,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R4:X58	Other Type	Icon set		NO
J4:P58	Other Type	Icon set		NO
B4:H58	Other Type	Icon set		NO

I will give the indirect function a try although I have tried it previously and for some reason, it didn't seem to do what I wanted!.

Thanks again

 

[Dan_W]

Ok - thank you for that. That helps a bit in clarifying things, but I think I need to look at it tomorrow before work to wrap my head around it properly, if you've not solved it before then.
I'm inclined to suggest a VBA solution, because otherwise that seems like a lot of formulas in play.

 

[mickyh]

Thanks, Dan
Your suggestion of indirect works and a little bit more research led me to use it with the Cell function which allows me to make the reference to the cells relative. this is what I've ended up with in B4

=(INDIRECT("'"&$B$1&"'!"&CELL("address",C8)))

Thanks again

 

[Dan_W]

You're welcome. Another function that might be useful is the OFFSET function.