Using a Sheetname to do a vlookup

Hi Andries,

Try this:

Note: nome.arquivo is file.name.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SheetName</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Sheet7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=MID(<font color="Blue">CELL(<font color="Red">"nome.arquivo",A2</font>),FIND(<font color="Red">"]",CELL(<font color="Green">"nome.arquivo",A2</font>)</font>)+1,1000</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

I was just looking into something similar to this earlier today. The MID function mentioned by markmzz would work, also it looks like you can use a right function as well:


For my purposes all of my sheet names are 6 characters long so the most complex part of the task (determining the number of characters in the sheet name) is unnecessary in this instance and it appears I can use the following formula:


Hopefully these work, best of luck to you!

Hi markmzz & mark5767

Thank you very much both these function works very well

Andries said:

Hi

I am looking for a function to return the sheet name...in other words if sheetname = 7X OR 7Y OR bla bla bla then vlookup....

Click to expand...

Since a sheet name is limited to no more than 31 characters:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)

Andries said:

Hi

I am looking for a function to return the sheet name...in other words if sheetname = 7X OR 7Y OR bla bla bla then vlookup....

Click to expand...

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

yields the sheet name. If you want to look up a range on the sheet whose name this formula gives, you'll need to apply INDIRECT in your look up formula, as in the following example:

VLOOKUP(LookupValue,INDIRECT("'"&I1&"'!A1:C12"),2,0)

Andries said:

Hi markmzz & mark5767

Thank you very much both these function works very well

Click to expand...

Hi Andries,

More one problem solved and thank you for the feedback.

Markmzz

Hi Aladin Akyurek

Can you please explain your second fuction VLOOKUP(LookupValue,INDIRECT("'"&I1&"'!A1:C12"),2,0)

...sorry my knowledge of Excel functions is not very good. I am currently using this function
VLOOKUP('week 1 - ROW ALLOCATION'!A5,'7x'!$DH$3:$DY$2912,18,0))
but the LookupValue '7x'!(which is variable) needs to be looked for between about 15 other sheets.


I hope this explains it better

Andries said:

Hi Aladin Akyurek

Can you please explain your second fuction VLOOKUP(LookupValue,INDIRECT("'"&I1&"'!A1:C12"),2,0)

...sorry my knowledge of Excel functions is not very good. I am currently using this function
VLOOKUP('week 1 - ROW ALLOCATION'!A5,'7x'!$DH$3:$DY$2912,18,0))
but the LookupValue '7x'!(which is variable) needs to be looked for between about 15 other sheets.


I hope this explains it better

Click to expand...

There is a formula option with built-in functions, but a bit heavy handed...

Create a range that houses the relevant sheet names. Name this range SheetList via the Name Box. Then invoke:

=VLOOKUP('week 1 - ROW ALLOCATION'!A5,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!DH3:DH2912"),'week 1 - ROW ALLOCATION'!A5)>0,0))&"'!DH3:DY2912"),18,0)

Another option is to use THREED from the morefunc.xll add-in which you could install on your system if it's a 32bit system.

Hi Aladin Akyurek

At last I got it right....Thank you for your help