Solved Cell value equal to a sheetname

May 11, 2013 at 08:58:06
Specs: Windows 7
Hi there,
I have been working on a spreadsheet for a while now , and started off using formula. I have the following formula in one worksheet a considerable amount of times

=IF(F1=(LOOKUP(F1,Sheet2!$5:$5)),Sheet2!R29,0)

F1 = Date, therefore I m looking for the date in Sheet2 along Row5, if found, use the value in Sheet2 cell R29, if not then value = 0. The next formula would be 10 columns across and would read =IF(F1=(LOOKUP(F1,Sheet2!$5:$5)),Sheet2!AB29,0) and so on , 52 times across and 48 rows

The 48 Rows will be referenced in Column C with the title of different worksheets
Sheet2 could equal James Hunt (Range C5)
Sheet3 could equal Niki Lauda (Range C6) etc

What I am looking to do is to make the formula generic so that instead of the sheet name being in the formula, the formula has so confirm the sheet name from column Range

Any ideas


See More: Cell value equal to a sheetname

Report •


✔ Best Answer
May 13, 2013 at 13:55:48
Mike & DerbyDad03
Between the pair of you, We have resolved the issue
Who gets the 'Set best Answer'
Mike for giving almost the correct format , or DerbyDad03 for refining it.
Let me know, but thanks guys for your help


#1
May 11, 2013 at 15:06:29
If I understand you correctly,
the Sheet Name is in cell C5 and is the Text String, James Hunt.
If this is correct, then you can use the INDIRECT() function

Something like this:

=IF(F1=(LOOKUP(F1,INDIRECT(C5&"!"&$5:$5)),Sheet2!R29,0)

If your Sheet Names have spaces, you may need to add quote marks
but it might be simpler to add an underscore to your sheet name.

MIKE

http://www.skeptic.com/


Report •

#2
May 11, 2013 at 23:47:25
Thanks for the response, but that does not seem to work. I have not had time to look at the function INDIRECT but will as soon as I can

=IF(F1=(LOOKUP(F1,INDIRECT(C5&"!"&$5:$5)),Sheet2!R29,0)

Where Sheet2 was referenced twice in my original formula, can the INDIRECT function be used twice in the formula, ie Sheet2!R29 becomes
INDIRECT(C5&"!"&R29) as well


Report •

#3
May 12, 2013 at 00:17:21
INDIRECT will take whatever arguments you supply and attempt to use them as a cell reference. As long as the cell references, text strings and formulas can be combined to create a properly formatted cell reference, it should work.

With these values...

A1 = She
A2 = et
A3 = !
A4 = B1
B1 = 4

...this formula will return the value in SHEET2!C4

=INDIRECT(A1&A2&SUM(1,1)&A3&"C"&INDIRECT(A4))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 12, 2013 at 06:22:07
can the INDIRECT function be used twice in the formula

Yes, just add the second INDIRECT in your formula something like:

=IF(F1=(LOOKUP(F1,INDIRECT(C5&"!"&$5:$5)),INDIRECT(C5&"!"&R29),0)

Remember, if your Sheet Names have spaces, you may need to add quote marks, something like:

=IF(F1=(LOOKUP(F1,INDIRECT("'"&C5&"'!"&$5:$5)),INDIRECT("'"&C5&"'!"&R29),0)

See how that works.

Edit Added:

you may need to add quote marks

That should read single quote marks,


MIKE

http://www.skeptic.com/


Report •

#5
May 13, 2013 at 01:49:15
I cant get this to work because I probably have not explained this properly. Let me try again

Sheet 1
Cell C5 represents the name of a work sheet and so on down to C53
Lets say C5 has the worksheet name 'Tony Gibb'

F1 = 'a date, P1 = 'a date' and so on (every 10 columns)

The worksheet 'Tony Gibb' has the 'date' entered across 'Row5' but is not consistently every 10 columns as on Sheet1, but is variable to 10 row increments. The code I have for this is as follows

=IF(F1=(LOOKUP(F1,'Tony Gibb'!$5:$5)),'Tony Gibb'!R29,0)

This formula works very well indeed.
Sheet1 C5 = Sheetname 'Tony Gibb' Cell G5 = 'the result of the above formula'

In the above formula, what I would like to happen is to change 'Tony Gibb' as a direct entry, and have the sheetname of C5 entered.
Assume that the name 'Tony Gibb' could change to 'James Hunt'


Report •

#6
May 13, 2013 at 03:20:21
The second formula I suggested should give you what your looking for:

=IF(F1=(LOOKUP(F1,INDIRECT("'"&C5&"'!"&$5:$5)),INDIRECT("'"&C5&"'!"&R29),0)

What ever TEXT string is in cell C5 should translate to a Sheet Name.

Make sure your sheet name is exactly the same as what is entered in C5
this includes spaces before and after.

If you have Excel 2007, on the Ribbon, under the Formula Tab
use the Evaluate Formula and walk thru the formula and see what is happening.

MIKE

http://www.skeptic.com/


Report •

#7
May 13, 2013 at 10:08:35
Mike,
There seems to be a problem with the second INDIRECT statement, in that it is referencing to cell R29 on the worksheet, and not to the named worksheet in C5

Also C5 has a statement as follows =LEGEND!AP3

This actually provides the worksheet title in a number of other worksheets, so column AP is a list of the worksheet titles in worksheet LEGEND

Tony


Report •

#8
May 13, 2013 at 12:22:14
Mike,
the problem I have seems to be that this part of the formula &$5:$5 returns a value of 0 which in turn returns #Ref. The formula then fails to be evaluated any further

The data for these fields is yet to be entered. They are pasted as values when the event takes place. I think I may have to rewrite the formula to account for this, but I'm struggling. Any ideas


Report •

#9
May 13, 2013 at 13:09:00
I'm jumping in late here, but I see a few things wrong with this formula:

=IF(F1=(LOOKUP(F1,INDIRECT("'"&C5&"'!"&$5:$5)),INDIRECT("'"&C5&"'!"&R29),0)

First, you don't have matching parentheses. I see 5 opening parenthesis but only 4 closing. I pretty sure that the open parenthsis before LOOKUP is not required. So let's start with this:

=IF(F1=LOOKUP(F1,INDIRECT("'"&C5&"'!"&$5:$5)),INDIRECT("'"&C5&"'!"&R29),0)

Next, let's look at this:

INDIRECT("'"&C5&"'!"&$5:$5)

Since the $5:$5 is not within the quotes, it is not going to be used by the INDIRECT function as I think you want.

The same holds for INDIRECT("'"&C5&"'!"&R29)

With that syntax, INDIRECT is going to use the value in R29 of the sheet in which the formula resides, not the text string "R29"

In other words if C5 contains Tony Gibb and R29 contains 12, the INDIRECT function will try to use 'Tony Gibb'!12 as a cell reference and return a #REF error.

The proper syntax requires that you include the cell reference as part of the text string that the INDIRECT function is trying to evaluate:

INDIRECT("'"&C5&"'!R29")

You probably have to change the first INDIRECT function also. I can't test anything right now, so I'll leave that up to you, but I think that the $5:$5, needs to be moved inside the quotes.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
May 13, 2013 at 13:55:48
✔ Best Answer
Mike & DerbyDad03
Between the pair of you, We have resolved the issue
Who gets the 'Set best Answer'
Mike for giving almost the correct format , or DerbyDad03 for refining it.
Let me know, but thanks guys for your help

Report •


Ask Question