Vlookup based on cell content

[AngleseyExcel]

=VLOOKUP(AA10,'Week 52'!$F$8:$F$105,1,0)

i'm using the above to check that selection on sheet 'Week 52'. but i want it to be dependent on whichever week number is in a certain cell. not fixed on one single sheet.

Can anyone give me any pointers on how this would be done ?

 

[etaf]

use an indirect() to get the TEXT week 52 from a cell

so would this be just a number or the full text week #number

=VLOOKUP(AA10,INDIRECT("'"&F1&"'!$F$8:$F$105"),1,0)

I have changed to use A10 in example

Book5
	A	B	C	D	E	F
1						week 52
2				Orginal
3				1		1
4
5
6
7
8
9
10	1
11
Sheet1

Cell Formulas
Range	Formula
D3	D3	=VLOOKUP(A10,'week 52'!$F$8:$F$105,1,0)
F3	F3	=VLOOKUP(A10,INDIRECT("'"&F1&"'!$F$8:$F$105"),1,0)

 

[jasonb75]

You would need to use INDIRECT for that, although as you're only validating existence I would use it with countif instead of vlookup, should be slightly more efficient if the formula is being used in a large number of cells.

=IF(COUNTIFS(INDIRECT("'Week "&A1&"'!$F$8:$F$105"),AA10),AA10,"")

If the sheets for weeks 1 to 9 are named as Week 01 etc, rather than Week 1 then you would need to use &TEXT(A1,"00")& instead of &A1&

 

[AngleseyExcel]

use an indirect() to get the TEXT week 52 from a cell

so would this be just a number or the full text week #number

=VLOOKUP(AA10,INDIRECT("'"&F1&"'!$F$8:$F$105"),1,0)

I have changed to use A10 in example

Book5
	A	B	C	D	E	F
1						week 52
2				Orginal
3				1		1
4
5
6
7
8
9
10	1
11
Sheet1

Cell Formulas
Range	Formula
D3	D3	=VLOOKUP(A10,'week 52'!$F$8:$F$105,1,0)
F3	F3	=VLOOKUP(A10,INDIRECT("'"&F1&"'!$F$8:$F$105"),1,0)

I've tried using the Indirect, but it either returns #Value! or #N/A not sure why ?

 

[AngleseyExcel]

Just a quick idea, but is it possible to get the indirect to work off a selection in a combo box, instead of the content of a cell ?

 

[etaf]

Not sure about using a combo box, been years since i played with forms or vba , in the early 90's

so the vlookup you have actually does return a value , if its hard coded without the indirect ?

lets see the formula
and what cells are referenced
ideally using XL2BB - see signature
Or on a share like dropbox or onedrive

 

[AngleseyExcel]

Not sure about using a combo box, been years since i played with forms or vba , in the early 90's

so the vlookup you have actually does return a value , if its hard coded without the indirect ?

lets see the formula
and what cells are referenced
ideally using XL2BB - see signature
Or on a share like dropbox or onedrive

is there any way i could send you a sample excel file ? and i'll explain what i;m trying to do ?

 

[AngleseyExcel]

is there any way i could send you a sample excel file ? and i'll explain what i;m trying to do ?

if you provide me with an email address i'll share it from my onedrive ?

 

[etaf]

we are not allowed to work offline here i'm afraid

Message Board Rules

Along with the Terms of Use you agreed to follow when registering, please follow these rules.

www.mrexcel.com

you can add a sample file, with no personal data here, via a dropbox or onedrive link

OR better still install the XL2BB addin and post here

 

[jasonb75]

I've tried using the Indirect, but it either returns #Value! or #N/A not sure why ?

If INDIRECT was the root cause of the error then it would be #REF!

#N/A means that there is no match for the lookup criteria in the range specified.
There is no reason for any of the suggestions provided to return a #VALUE! error unless there is an existing error in your data.

Just a quick idea, but is it possible to get the indirect to work off a selection in a combo box, instead of the content of a cell ?

If you want the sheet names as a dropdown so that you don't have to type them, data validation would be a preferable choice.