[justbuzz]

I'm using a spreadsheet that has 26 tabs (labeled 1,2,3,4... ) and I'm using a VLOOKUP command to accumulate data from each of these tabs in order to populate a trend analysis graph. Now, is there a way to take the VLOOKUP command (=VLOOKUP($AI$4,'1'!$A$8:$AX$298,$AJ$4,FALSE)) and have the '1' - which is the name of the tab I'm looking up - link to a cell? I'm using multiple VLOOKUPs and I'd like to have the tab number in the VLOOKUP hard linked to a group of cells that contain the numbers 1-26, so I can just have the column/ row number of the respective tab in the space of the '1'! Any help would be HUUUUGE! Thanks in advance!

[JBeaucaire]

You would use the INDIRECT() function to create a cell/sheet reference from text strings concatenated together.

=VLOOKUP($AI$4, '1'!$A$8:$AX$298, $AJ$4,FALSE)

To make the part in red dynamic and get the sheet name from a cell, let's say A1 in this example, then like so:

=VLOOKUP($AI$4, INDIRECT("'" & A1 & "'!$A$8:$AX$298"), $AJ$4, FALSE)

[justbuzz]

JBeaucaire! THANK YOU SO MUCH! This formula worked perfect and will save so much time with all future projects! You are awesome. Thanks again for taking the time to answer this question so precisely!

Have two more questions for you though, if you are able to help with these it would again be so greatly appreciated! Thanks again for the help above!

1.) I am trying to use a similar formula to the one you provided as a "Chart Data Range" while selecting data for a chart. I am using if statements to select a particular tab (again numbered 1-26) based on particular returns. I've tried variations of the above formula with no luck. Any idea how to use a formula to choose a particular tab (referenced by a cell) and then a data range. The formula that I am using for now is listed below and I am having to manually adjust the tab number based on returns and would like the tab to be locked to a particular cell. THANKS!

Chart data range: ='1'!$AL$4:$AS$29

2.) Also, any idea how to write a macro or do you know of another way to simultaneously "protect" and "unprotect" multiple tabs without having to right-click each one individually and input the password for each tab?? Thanks again for all the help!

[JBeaucaire]

1) I would need to see your workbook to have any chance at figuring that out.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

2) Protect and unprotect all sheets, or just the ones you've selected?