INDIRECT to Reference Worksheets #Ref Error

**McToons** · 09-03-2011, 04:54 PM

The file in question has multiple "detail" worksheets and a single worksheet as a summary.

The summary has in column A the names of all the detail worksheets listed and I'm trying to use that via INDIRECT to reference the correct worksheets in the formulae in columns B-O, rather than adjusting each formula manually. The workbook will grow over time so it'll be very useful when adding in new detail sheets.

Currently one of the formula's in question reads...

=IF((INDIRECT("'" & A1 & "'!$BD$3"))=(TODAY()),"Due",(IF((INDIRECT("'" & A1 & "'!$BD$3"))<(TODAY()),"Over Due",(IF((INDIRECT("'" & A1 & "'!$BD$3"))>(TODAY()),"Not Due","")))))

Obviously it's comparing the current date to a due date within the detail sheet and giving me a response of whether it is Due or not. Right now it's just giving me back a #Ref error and I'm rusty enough on Excel to not see why.

Many many thanks in advance for any help on this.

**barryleajo** · 09-03-2011, 05:13 PM

Hi McToons
Check that there is a valid sheet name in col A on the row where the error is being thrown

**McToons** · 09-03-2011, 05:16 PM

Originally Posted by barryleajo

Hi McToons
Check that there is a valid sheet name in col A on the row where the error is being thrown

Sadly that was the first thing I did check, I've retyped the name in both the column and the actual sheet name, they match 100%.

[Edit]

Nevermind, ignore me, went away had a beer and came back, saw my mistake straight away and it's rather embarrassing. Thanks so much anyway you did kind of point me right.