Cell reference in worksheet formula???

[hightime]

I've had help from this forum before (thank you) and now I would like a little more if poss....

Basically I have about 120 cells in a spreadsheet which reference 120 equivalent cells from a different workbook. It's a timesheet spreadsheet that 'pulls in' the data from individuals spreadsheets where they record their overtime etc etc.

Anyway, every cell has the filename in the formula...

...like this....

=IF('[Name-Jan.xls]MONTHLY TIME SHEET'!$G$16>0,('[Name-Jan.xls]MONTHLY TIME SHEET'!$G$16),"")

This code either pulls in the value or records a blank. Works OK.

I would rather the filename was 'referred to' rather than embedded in the formula. That would mean I could change the filename in question by modifying ONE CELL rather than messing about with all the various cells that use it.

I will run some code to do all these changes BUT I can't work out what syntax to use.

It 'should' look something like this (obviously doesn't work)

=IF('[$a$1]MONTHLY TIME SHEET'!$G$16>0,('[$a$1]MONTHLY TIME SHEET'!$G$16),"")

In this case the name of the file is stored (and easily modified) from Cell $A$1

Has anyone got any ideas ??

Many thanks in advance to all the helpful people out there. Still amazes me that there are so many people who will take time out to help a stranger....
...anyway, enough advance grovelling for now.

Oh yes, I'm using Excel 2000 on a Windows 2000 PC

Cheers

Paul

[mark007]

You could make use of Indirect which allows you to pass a string as a cell refernce:

=IF(indirect("'["&a$1&"]MONTHLY TIME SHEET'!$G$16")>0,indirect("'["&a$1&"]MONTHLY TIME SHEET'!$G$16")>,"")

[Jacob Hilderbrand]

Yeah, Indirect is the way to go. You should use Absolute References ($A$1) for both row and column so you can drag the formula across rows and columns without having the reference changing.

There is also a typo in Mark's formula.

=IF(INDIRECT("'["&$A$1&"]MONTHLY TIME SHEET'!$G$16")>0,INDIRECT("'["&$A$1&"]MONTHLY TIME SHEET'!$G$16"),"")

[mark007]

There is also a typo in Mark's formula.

Figures - I'm dangerous near a keyboard

[hightime]

At the risk of sounding like a teenager (fat chance at my age)...you guys rock!!

It works BUT.....I have another problem.

If the master cell (say) $A$1 is the exact filename "Name-Jan.xls" then it works fine.

Unfortunately I have 'built' the filename in $A$1 using the CONCATENATE function. It takes the 'name' from one cell and combines with the 'month' from another to 'Build a complete filename in $A$1

If I do that I get a #REF error in the formula cell.

I could possibly work round things as they are, but would be nice to know if you have any ideas how my problem might be addressed as it stands.

Thanks once again for a very SPEEDY response.

Regards

Paul

[Zack Barresse]

Hi Paul,

Post the formula you have in A1.

You will get the #REF! error when/if you are referencing certain cells in a formula and delete any of the precedents.

Example, do this; type 10 in A1, type =A1 in cell B1. They should now match. Now select and delete the entire column A. B1 will shift left and is now A1, but the reference to the formula has been deleted so you will get the #REF! error.

That's why this is a dependent issue.