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) etcWhat 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

✔ 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

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() functionSomething 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

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

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.

can the INDIRECT function be used twice in the formulaYes, 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 marksThat should read

singlequote marks,

MIKE

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'

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

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 C5Also 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

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 furtherThe 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

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

valuein 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.

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

Ask Your Question

Weekly Poll