Help with formulas using different work sheets

[Balyssia]

Hello,

I was wondering if anyone can help me;

I am trying to collate data used in work sheets 1 and 2 into a chart in work sheet 3.

The formula I am wanting to use is one to create a tally of how many times a text is mentioned in a row;

=sum(IF(F12:F1266="Ellis",1,0))

This formula works fine in the sheet the data is in but I want to move it across to another tab so it's in a table with the relevant info.

How do I keep this formula but move it to a different tab?

I will be doing this for alot of different words!

Any help would be greatly appreciated

Thank you

Lys

[akhileshgs]

a simple solution is to replace all '=' sign with # and copy paste. Then replace the # sign with =
I do this everytime..

[Balyssia]

Thanks for your reply.

Just tried doing that and it just tried to retrive the data from the sheet it had been copied into, not the original one?

[akhileshgs]

HI,
Have a look at this

[akhileshgs]

Otherwise you will have to fix each and every cell by using $ (F4)

[martindwilson]

=sum(IF(F12:F1266="Ellis",1,0)) is a strange way to count text
you should use =COUNTIF(F12:F1266,"ellis")
fyi i just copy the formula into notepad then recopy back to excel

[akhileshgs]

But I think he want to retain the ref of Sheet 1 even if we copy to sheet 2

[Balyssia]

Does that not apply for moving a formula around the same work sheet but in different locations?

I need to create a formula that pulls data from a different work sheet.

[martindwilson]

enter the formula like this
=COUNTIF(Sheet1!$F$12:$F$1266,"ellis")
if you have =COUNTIF($F$12:$F$1266,"ellis") then put the cursor just before $F$12:$F$1266
select any other sheet with mouse then select back to the sheet the formula is in press enter
the sheet name will be added
you can now copy paste the formula

[Balyssia]

Okay,

I have attached a test version of what I am trying to do;

I want to tally up the quantity of tests and examples from both sheets 'Test A' and 'Test B' into 'Test Total'

I want the formula there so that I can add more data to 'Test A' and 'Test B' and it will automatically update the 'Test Total'

Thanks

[akhileshgs]

Or better solution is replace '$F$12:$F$1266' with 'Sheet1!$F$12:$F$1266'

[Balyssia]

That seems to be working! thanks

[akhileshgs]

:-) Glad that we could help you.
Please make this thread as solved by Edit>>Advanced and in the dropdown-solved