Link cell from a dynamically generated sheet to a master sheet

**stockfeed** · 08-03-2012, 08:34 PM

Hello everyone, this is my first time posting here, i hope this is in the right place.

My problem is as follows:

I need to generate worksheets for various tasks that are separable quoted items on a larger project. i then need to have the total of each of the newly generated task sheets link back to the master sheet, where the subtotals are added together and a project total is calculated. I have come across some code that allows me to generate the sheets with the names of the particular tasks on to the mastersheet, but i cant get the total of each task sheet to link back to the mastersheet automatically. The task sheets are generated from a template which in turn is linked to a rates sheet.
Any help would be greatly appreciated.

**stockfeed** · 08-04-2012, 07:05 AM

ok so i got it to work using indirect, but its acting weird now. sometimes the function will work, and sometimes it wont. cant figure out why. anyone got any ideas?
P.S. i guess this should be moved out of the VBA/Macros sub forum as it doesnt apply here anymore

EDIT- It appears that if i write more than one word in Column B, the Function doesnt recognise it and calls up a null value. Any way around this?
ie-run the macro and two tables are created, digholes and dig holes.
digholes-returns the value in the digholes worksheet
dig holes - doesnt return the value in the dig holes worksheet.

**Cutter** · 08-04-2012, 11:03 AM

A sheet name with space(s) in it must be enclosed in single quotes.

To refer to a sheet called dig holes you would need: 'dig holes'!

To avoid the need for quotes you can use an underscore in place of the space: dig_holes!

**stockfeed** · 08-04-2012, 07:03 PM

ok cool thanks for your reply Cutter, I figured spaces would cause problems, however the real issue is i dont know what my sheet names are going to be until i create them. im using:

=IF(ISERROR(INDIRECT(B13&"!$I$4")),0,INDIRECT(B13&"!$I$4")) where B13 contains the name of the sheet.

If i put 'B13' it throws an error, so am i stuck with single words or underscores? Am i getting into VBA territory if i want it to refer to a not yet created table which may or may not have spaces in it?

**Cutter** · 08-04-2012, 07:12 PM

You need to add the single quotes as part of the string value:

=IF(ISERROR(INDIRECT("'"&B13&"'!$I$4")),0,INDIRECT("'"&B13&"'!$I$4"))

If you don't need pre-2007 compatibility you can use:

=IFERROR(INDIRECT("'"&B13&"'!$I$4"),0)

**stockfeed** · 08-04-2012, 07:24 PM

Thanks Cutter, worked a treat! I can fnally relax now!

**Cutter** · 08-04-2012, 07:26 PM

With a Fosters?

You're welcome. Please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

EDIT: Thanks for the 'star tap'.