formula help

[JOEE1979]

Hi, I was wondering if there is a way to use cell(D3) to incorporate it into my formula in cell(D8).
If you look at the formula in cel(C8), that's the result I'm looking for

Weekday Bus Cycling - new - Copy.xlsm
	B	C	D	E	F
2	Page	3	4	5	6
3	Garage	Aberfoyle-MON-WED	Aberfoyle-THURS-FRI	Aberfoyle-SAT	Aberfoyle-SUN
4	Next Run	Tom	Tom	Tom	Tom
5	Units Ready	1
6	Remainder	0	0	0	0
7	Good Until	Tom	Tom	Tom	Tom
8	1	0:00	#REF!
9	2	0:00
10	3	0:00
11	4	0:00
12	5	0:00
Planner

Cell Formulas
Range	Formula
C3:F3	D3	=INDEX(ListSheets,D2)
C4:F4	D4	=IFERROR(SMALL(D8:D1048576,COUNTIF(D8:D1048576,"<"&$C$1)+1),"Tom")
C6:F6	E6	=COUNTIF(E8:E1048576,">="&$C$1)
C7:F7	E7	=IFERROR(SMALL(E8:E1048576,COUNTIF(E8:E1048576,"<"&$C$1)+E5),"Tom")
D8	D8	='D3'!D5
C5	C5	='Aberfoyle-MON-WED'!K1
C8:C12	C8	='Aberfoyle-MON-WED'!D5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AO8:AX60,AM61:AX108,AM9:AM60,C9:AL108,C109:AX1048576,C8:AM8	Cell Value	>$C$1	text	NO

 

[awoohaw]

do you have a worksheet named D3?

 

[JOEE1979]

do you have a worksheet named D3?

No, I wanted to see if I can somehow incorporate the name that is retrieved in "D3". That way I can be able to fill a sheet out with over 1000 cells much easier

 

[awoohaw]

okay, you want to use the INDIRECT function then: =INDIRECT(D3 & "!D5").
NOTES: 1. The indirect function is volatile and can really slow your workbook down (It recalculates EVERY time the enter key is pressed) If you have a lot of formulas like this it is not advised.
2. On a more pertinent perspective... the above is hardcoded cell D5 on what ever sheet is named in D3. If you want the row to move down with the formula then you need to do some math. I usually do the rows method. If starting this formula in row 8... =INDIRECT(D3 & "!D" & ROW(D8)-3). Also, Sheet names with spaces must be wrapped in Single Quotes (I'm unsure if excel ignore these if the sheet name has no spaces), so you may have to do this: =INDIRECT("'" & D3 & "'!D" & ROW(D8)-3)

 

[JOEE1979]

okay, you want to use the INDIRECT function then: =INDIRECT(D3 & "!D5").
NOTES: 1. The indirect function is volatile and can really slow your workbook down (It recalculates EVERY time the enter key is pressed) If you have a lot of formulas like this it is not advised.
2. On a more pertinent perspective... the above is hardcoded cell D5 on what ever sheet is named in D3. If you want the row to move down with the formula then you need to do some math. I usually do the rows method. If starting this formula in row 8... =INDIRECT(D3 & "!D" & ROW(D8)-3). Also, Sheet names with spaces must be wrapped in Single Quotes (I'm unsure if excel ignore these if the sheet name has no spaces), so you may have to do this: =INDIRECT("'" & D3 & "'!D" & ROW(D8)-3)

It didnt work, but I modified it to =INDIRECT("'" & D3 & "'" & "!D5")