HELP! Use cell contents rather than cell address in formula.

[SubtleNoob]

I've tried looking round and there have been a few posts similar but the questions I could find didn't have the answer I needed within them.

Its pretty simple and I have a fairly reasonable understanding of macros and can do some basic stuff so VBA is as welcome as a formula in any solutions.

I have the abbreviated layout below:

[TABLE="width: 498"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]qq2[/TD]
[TD]=IFERROR(VLOOKUP("ff",'A2'!A1:Z99,2,FALSE),0)[/TD]
[/TR]
[TR]
[TD]ss3[/TD]
[TD]=IFERROR(VLOOKUP("ff",'ss3'!A1:Z99,2,FALSE),0)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 626"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Column 1 is the sheet names Column 2 is the formula I have. "ff" is the thing I am looking for. The underlined is where I have a problem. I cannot get excel to use the cell contents in the formula which means I get an error because it ends up looking for sheet "A2" rather than "qq2" or "ss3". Green works Red doesnt.

I tried using CELL but it gave me an error.

=IFFERROR(VLOOKUP("ff",'CELL("contents",A8)'!A1:Z99,2,FALSE),0)


I have tried making a macro using copy and paste inside the formula. It just overwrites the next cell when I run it, as well as moving the the search down one:

Sub Macro4()
'
' Macro4 Macro
'


'
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "qq2"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(""ff"",'qq2'!R[-3]C[-1]:R[95]C[24],2,FALSE),0)"
ActiveCell.Select
End Sub



I have a good few hundred sheets to reference on this workbook so entering manually would be very long and because of frequent changes in names and so on it would be hard for my successor to maintain. Thank you very much for your time reading and help if you can offer it.

Any additional questions please ask.

 

[njimack]

=iferror(vlookup(indirect(a2&"'!a1:z99"),2,false),0)

 

[SubtleNoob]

Thank you very much for your help! I had to fiddle a little and got it to work using the below so I just took out the ('):


=VLOOKUP("ff",INDIRECT(A2&"!A1:Z99"),2,FALSE)


It has however caused another error. A reference error for a few of my values. which are:


r1e, r3c, r8q etc


I am pretty sure it is due to relative referencing but am yet to find a setting to stop it from working. Do you or anyone else have advice on this?


Thank you again Neil you saved me a lot of time. 10 odd cells is a lot better than hundreds.

 

[Jonmo1]

Not sure why, but I think it's trying to interprate r1e as a cell reference or somethng...

Put apostrophe's around the sheetname
='r1e'!A1:Z99

=VLOOKUP("ff",INDIRECT("'"&A2&"'!A1:Z99"),2,FALSE)

 

[SubtleNoob]

Thank you for the suggestion Jonmo1 alas it seems to be persistent.

I do get a different error now though: #N/A. The fx breakdown gives "r1e'!A1:Z99" I think the apostrophe is the problem but also the solution and I cant seem to remove it without returning to the original #REF! error.

I did try changing the contents of cell A2 to 'r1e and variations but alas no dice.

Any further suggestions would be gratefully received if not, thank you very much for trying.

 

[Jonmo1]

If it's returning #N/A now, that's a new problem and has nothing to do with the indirect function.

#N/A errors mean that it didn't find an exact match to the lookup value.

Assuming A2 was equal to r1e, then
What does this return

=VLOOKUP("ff",'r1e'!A$1:Z$99,2,FALSE)

 

[SubtleNoob]

Sorry this reply took me so long been way too busy, though I suppose it is my cross to bear haha. It comes back with the correct value.

Such that if "ff" had "3" next to it sheet r1e =VLOOKUP("ff",'r1e'!A$1:Z$99,2,FALSE) returns "3".

Thank you for your continued help.

 

[Jonmo1]

Thank you for your continued help.

You're welcome

It comes back with the correct value.

Glad it worked out for you.