Indirect formula

[mahmed1]

Hi Guys,

I have some sheets named <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>SumWk1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk4</TD></TR></TBODY></TABLE>

and also have values on those sheets

I have a main sheet and on that sheet i have a data validation <TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>SumWk1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>SumWk4</TD></TR></TBODY></TABLE>

What i want to do is change the data validation and then give me the value in that cell on that sheet but i cant get it to work.

This is the formula i have. =INDIRECT("'"&$B$3&"!"&"B"&ROW(A3))

 

[Domenic]

It looks like you're missing an apostrophe. Try...

=INDIRECT("'"&$B$3&"'!"&"B"&ROW(A3))

 

[mahmed1]

it looks like you're missing an apostrophe. Try...

=indirect("'"&$b$3&"'!"&"b"&row(a3))

thank you sir

 

[mahmed1]

It looks like you're missing an apostrophe. Try...

=INDIRECT("'"&$B$3&"'!"&"B"&ROW(A3))

How can i amend the formula so the "B" turns to C, D E etc when copied accross?

 

[T. Valko]

How can i amend the formula so the "B" turns to C, D E etc when copied accross?

One way...

=INDEX(INDIRECT("'"&$B$3&"'!3:3"),COLUMNS($A1:B1))

 

[mahmed1]

Hi i managed to do it with this formula?

=INDIRECT("'"&$F$2&"'!"&CHAR(ROW($A$65)+COLUMNS($A$1:A1)-1)&ROW(A3))

Is there a shorter formula i could use?

 

[mahmed1]

One way...

=INDEX(INDIRECT("'"&$B$3&"'!3:3"),COLUMNS($A1:B1))

Hi thank you, i posted my message before seeing your formula

 

[T. Valko]

Hi thank you, i posted my message before seeing your formula

You're welcome. Thanks for the feedback!

 

[jasonb75]

R1C1 references can be useful to make indirect ranges relative, for example

=INDIRECT("'"&$B$3&"'!"&"R[-1]C[1]",0)

This example would return B3 if the formula was in A4.