copy formulas from 3 sheets to 1 sheet

[John Caines]

hello All.
have a question.
1st, I have searched the forum for a while on this,, and see lots of macros etc for this type of thing.

I'm not asking for a macro,, just clarity really.
I have 3 sheets of data.

1 sheet is labeled "2-PROJECTIONS (%)"
another is "3-PROJECTIONS (£)"
& the 3rd sheet is called "4-PROJECTIONS (THE END)"

What I'm looking to do is transfer almost half the formulas from each sheet to another sheet called;
"5-ADVANCED COMPARED"
There are probably well over 1500 different formulas,, and the cell location letters/numbers won't match,, they will be going into different cell locations into the "5-ADVANCED COMPARED" sheet.

My question is,
EXAMPLE:
=======
If I want to copy the formula in cell C16 in the sheet "2-PROJECTIONS (%)" (Without Quotes)

=IF(COUNTBLANK($G$6:$G$7),"",F59)

And have this show also in sheet "5-ADVANCED COMPARED",,, Cell D5,,,
What would Cell D5 Formula be please?

I did google it,,
and saw an answer that was =Sheet1!A1 so that would translate to;

=2-PROJECTIONS (%)=IF(COUNTBLANK($G$6:$G$7),"",F59)

I've tried this and the formula isn't accepted,, as the sheet name contains the % sign I think??
Also,, is it ok to have a spaces in the sheet name,, as there is a space between the word projection & (%)???

I have seen on another thread someone mention single quotes? They say that if the sheet name isn't a single word you need to include single quotes also?
I'm not 100% sure how this works.
If someone could just clarify what the above formula should look like then I can spend the next 1-2 wonderful hows in a text file & excel,, copy & pasting

many thanks to you all.
Just clarity really,,
Just want to be sure.
Many Thanks
John Caines

 

[John Caines]

Just tried 2 more attempts,, as I could see errors in what I tried 1st,, but neither worked

='2-PROJECTIONS (%)'!,IF(COUNTBLANK($G$6:$G$7),"",F59)

='2-PROJECTIONS (%)'!,=IF(COUNTBLANK($G$6:$G$7),"",F59)

Not sure what is wrong here?

 

[John Caines]

Still can't see why this doesn't work?
I'm trying it with the single quotes,, I think this is because there is a space between tab name PROJECTIONS & the (%).........
At least I think this is why I need single quotes.

='2-PROJECTIONS (%)'!,=IF(COUNTBLANK($AE$6:$AE$7),"",F59)

Just doesn't accept this,,, highlighting the IF?????

Anybody can show me the error of my ways please

If I can get the above to work,, I should be able to do the other 1,500 cells ok,, (I think)

 

[jeffreybrown]

Hi John,

In your example, wouldn't it be...

=IF(COUNTBLANK('2-PROJECTIONS (%)'!G6:G7),"",F59)

 

[John Caines]

Many Thanks JB,
Excellent!!.
i haven't tested it yet, as I need to start filling in the whole sheet,,
but it was accepted in the formula bar,, great stuff!

As a note;
Formula I have is now;

=IF(COUNTBLANK('2-PROJECTIONS (%)'!AE6:AE7),"",F59)

Not sure if it really needs to be;

=IF(COUNTBLANK('2-PROJECTIONS (%)'!$AE$6:$AE$7),"",F59)

I just tried the above,,, it was accepted as well...
i best try and find out what the difference is,, only a $ or 2

many thanks again JB,,
Now 2 hrs for copy & paste!