INDIRECT Issue re Worksheet Name

[chmm08]

I have been stymied with an INDIRECT function issue and feel like I am very close to resolution, but need assistance from an expert.

My workbook has a Summary worksheet that needs to pull from specific cell references in subsequent worksheets. The following formula works if the worksheet name exactly matches the cell reference (A11) from the Summary worksheet:

=INDIRECT("'" &A11&"'!K6")

(A11) is a six digit number (123456). The problem is that the worksheet names include the six digit number as the last 6 characters of the name - there are 8 different choices the enduser can use to name the worksheet but the last 6 characters will always be the 6 digits from A11 in the Summary worksheet.
Example of one of the worksheet names: 'B1.2 - 2 DORMANT CLOSE 105313'

Is it possible to use 'RIGHT' or 'TRIM' in the INDIRECT formula? I have tried a few options but recevie an error every time.

Thanks in advance!

 

[Andrew Poulsom]

Sorry, the worksheet name has to be exact.

 

[chmm08]

OK - I thought this might be the case. What if I list the 8 worksheet name options in my Summary worksheet - out to the right of my Summary table in 8 successive cells - say M11:T11. Is it possible to nest a series of INDIRECT functions in my formula or combine with VLOOKUP?

 

[BenMiller]

What exactly are you trying to accomplish?

 

[Andrew Poulsom]

Sorry, I can't think of a way to do that. You can't VLOOKUP a worksheet name.

 

[BenMiller]

I think what chmm08 means is to enter the sheet names in cells and use a Vlookup to get them, with an INDIRECT wrapped around it. That would work.

 

[chmm08]

Yes - that is what I mean...

Complicated situation - I'll try to explain:

The workbook will have a Summary Tab at the beginning that will be (hopefully) automatically completed for a series of rows as a user enters data into the successive worksheets. Each row in the Summary tab represents a location designated with a 6 digit number: 123456, 123457, 123458, etc..... Columns across this row will be populated from a specific worksheet chosen by the user.

The user will choose only one worksheet of eight choices to complete for each row depending on their opinion of the correct scenario to use for that Location. The workbook I provide to the users will have each of the eight worksheets designated with the worksheet name ending in 999999. As the they choose the desired worksheet scenario they will rename the worksheet to include the corresponding six digit number from the Summary worksheet column A: 123456, 123457, 123458, etc.....

I do not know which of the 8 scenarios they will choose, thus my quandry in trying to get the formula to find the matching worksheet for each location.

It seems like this should be possible - but understand it is WAY over my head. Hard to explain, too. I'm trying to save the end user time and possible transcription errors by having the Summary tab automatically populate.

Thanks!

 

[BenMiller]

Try posting a sample of your data with Excel Jeanie.

 

[chmm08]

OK. Excel Jeanie is new to me - I've downloaded and activated, just need a minute to figure out how to post a sample.

Thanks!

 

[chmm08]

Hmm - this is a truncated test version that I have added some notes to. Does this make sense to you? In columns J,K,L are the names of the worksheets that I added as a possible lookup reference. Row 11 worked because I created an exact worksheet name match to Column A.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
9		Milestone 1			Milestone 2			etc?		I have created an index of the possible sheet names here..
10		Deliverable	Year	Cost	Deliverable	Year	Cost
11	123456	Milestone to be defined by Contractor	2012	400	N obtained	2013	600			B1.2 - 2 DORMANT CLOSE 123456	B1.2 - 3B1.2 - 4 >RA CLOSE 123456
12	123457	#REF!	300							B1.2 - 2 DORMANT CLOSE 123457	B1.2 - 3B1.2 - 4 >RA CLOSE 123457
13	123458		#REF!							B1.2 - 2 DORMANT CLOSE 123458	B1.2 - 3B1.2 - 4 >RA CLOSE 123458
Summary