hightime
10-19-2004, 03:27 AM
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):confused:
=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 ?? :help
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
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):confused:
=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 ?? :help
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