Way to show certain rows/columns in another worksheet?

[jent]

I manage an Excel spreadsheet with extensive information on the countries of the world and their embassies in the U.S. I'll spare the boring details and give an example screenshot of what I want.

Essentially, I'm currently managing a master file that I regularly update with changing statistics and information, and I also have a second file where I do the same thing, but only for certain countries (European Union countries and potential candidate countries, for example). The thing is, updating the second file leads to a duplication of work because I already update the stats and info (such as the last date my organization called the embassy) for all the countries.

I'd like to know if it's possible to somehow create a second worksheet that displays only certain rows and when doing so, displays only select columns for those rows. My EU spreadsheet is barebones and contains only certain info, so I wouldn't need each tidbit for select countries/rows but rather just a few specific columns.

Attached is a screenshot of a sample master file and sample second file that I currently maintain as two separate spreadsheets. Is it possible to somehow do this in a single spreadsheet through two workbooks, allowing me to only update the long list and have the second workbook automatically reflect those changes? Thanks!

 

[Pharmscott]

This is easy to do. Just click the destination cell (the small workbook) then type the equal sign (=, no parentheses), then click the corresponding cell in the main workbook. Then note the resulting formula and fill your series down.

When you update the main workbook, the other will update automatically.

In my example, the main workbook is named "A".

 

[jent]

Thanks Pharmscott! A few quick follow-up questions.

I can do this over workbooks in the same spreadsheet. Can I do it over two spreadsheets as well?

Is there any way to do it not by linking cells (if that's the right word) but by setting criteria (for example, "if the row has 'EU' or 'PC' in the
'EU Membership' column")?

Thanks!

 

[exegete77]

Thanks Pharmscott! A few quick follow-up questions.

I can do this over workbooks in the same spreadsheet. Can I do it over two spreadsheets as well?

Is there any way to do it not by linking cells (if that's the right word) but by setting criteria (for example, "if the row has 'EU' or 'PC' in the
'EU Membership' column")?

Thanks!

Just to get the terminology straight. A workbook contains many worksheets., not the opposite which is what you wrote. It will make a difference if you have to reference the worsheet in the same workbook.

 

[Pharmscott]

Thanks Pharmscott! A few quick follow-up questions.

I can do this over workbooks in the same spreadsheet. Can I do it over two spreadsheets as well?

Is there any way to do it not by linking cells (if that's the right word) but by setting criteria (for example, "if the row has 'EU' or 'PC' in the
'EU Membership' column")?

Thanks!

1) Yes, you can do this over any combination: in the same "file" on separate sheets or on two "files" on separate sheets. My screenshot example was two different workbooks.

2) I'm sure you could set up IF/THEN logic but I'm not sure why you'd want to add the complexity. Once you link a few cells, fill down and you're done. And, cells update automatically.