Changing the sheet a formula is refering to.

[Sal_P]

I made a sheet (Sheet 2) where a lot of the formulas are referencing data from another sheet (Sheet 1). Is there a way to write the formulas so I can change the sheet they're referencing by changing a single cell? I want all my formulas to reference data Sheet 3 instead of Sheet 1 by changing a cell, can this be done?

I hope I explained myself,

Thank you!

 

[AC PORTA VIA]

use ctrl & f to find and replace

 

[etaf]

if you setup the formula to use indirect() so the sheet is obtained from a cell , may work

do you have a sample sheet you can load here using XL2BB ?
or put on a share like onedrive/dropbox

heres an example - BUT not the range is in "" , so it get a lot more complicated if you want the range to change as you copy/paste the formula

this is a countif formula
and if you change C1 to sheet2 or any other name , then it will use that sheet name

Book6
	A	B	C	D	E
1	4	Fred	sheet1		John
2					fred
3					John
4					fred
5					fred
6					John
7					fred
Sheet1

Cell Formulas
Range	Formula
A1	A1	=COUNTIF(INDIRECT("'"&C1&"'!$E$1:$E$8"),B1)