Changing linked worksheet and range of cells

[Molly66]

Hello

I have a spreadsheet (master spreadsheet) which counts the number of times
individual countries appear in one column on a linked spreadsheet. The
linked spreadsheet and range of cells always changes. How can I update my
master spreadsheet to count the number of times a country appears in the new
range of cells in the new linked spreadsheet? I figured out how to update
the link to a new spreadsheet but not how to change the range of cells.

 

[Barb Reinhardt]

Try this

=countif(Sheet2!A:A, Sheet1!A2)

Sheet2!A:A contains the source data
Sheet1!A2 contains the country you want to count.

 

[Molly66]

Hi Barb

Thanks for your quick response. Sorry, I've probably not been clear. My
function to count the countries is fine, it's just when I try to update my
links to a new spreadsheet and new range that I don't know what to do. I
have used the edit/link option to update my master spreadsheet to look at a
new spreadsheet, which is fine, but I want to change the range as well.

Thanks in advance

 

[Barb Reinhardt]

I'm not following the specifics of your request? Do you have the countif
function on the master page? What do you mean by "update links". What
links do you want to update? What specifically is in them? Could you
replace those links with the "hyperlink" function somehow?

 

[Molly66]

Hi

Yes the countif function is on the master sheet. I do mailouts where I
receive a mailing list of multiple addresses to many different countries. I
use my master sheet to calculate mailing costs per country by linking it to
this. My master sheet has a country list showing for example
Column A Column B
Austria 25
Belgium 12
Denmark 10
Finland 4
France 53

and so on. The figures in column B are calculated by inserting the Countif
function under column B to count the number of times Austria etc appears on
my mailing list spreadsheet (this is what I call my linked spreadsheet).
My mailing lists shows for example:
ColumnA Column B
Mr B Smith France
Mrs J Adams Finland
Mr L Marshall Austria


The problem I have is that I receive different mailing lists and I would
like to be able to change the master sheet to show the new count of countries
as per the new mailing list, still using the countif function but calculating
it from the new mailing list and the new range of cells. Does that make
sense?

I don't know anything about hyperlinks. As you can probably guess I'm used
to using exel in a basic form but I'm almost there with this, I just need the
last step so that I can link my master sheet with a new range in a new
mailing list.

 

[Barb Reinhardt]

Can I assume that the new mailing list is a worksheet that you've added to
your workbook, or separate workbook?

Barb Reinhardt

 

[Molly66]

It's a separate workbook.

Thanks very much for taking the time to help, I appreciate it.

Regards--
Molly

 

[Dave Peterson]

First, I don't understand what you're doing, but there are some functions that
will only work if the sending workbook is open.

=countif(), =sumif(), =indirect()

are a few.

But there can be replacement formulas that may work for you:
=sumproduct() or =index()

If you can get your formula to work when the sending workbook is open, post that
working formula and maybe someone can give you an alternative.