Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: *** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Insta

06 Apr 2014   #1

win 7
*** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Insta

1) worksheet A

sheet1 has a number in cell a1

on sheet2, you want to reference the a1 cell in sheet1. you want to do this in sheet2 on cell a1. you want "100-{sheet1a1}"

2) worksheet B

on sheet100, you want to count how many cells has something in it from cell a2 to a200

cell can be empty

can you do this with text in cells? or numbers? or a mix?

you want to know how to do numbers though.

~ end ~

sorry don't know what to google, don't use this function in excel much.. googling is very messy, confusing, and scattered.

please explain clearly. please do not use excel terms. won't understand.

excel 2013

~ extra ~

an excel cheatsheet for this would be helpful since i'll forget pretty soon from not using that function or whatever much.

quality > speed

good links are good (sharing is caring)

*** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Instances Of Anything ~ ***

My System SpecsSystem Spec
06 Apr 2014   #2

Microsoft Community Contributor Award Recipient

Windows 7 Home Premium SP1, 64-bit

Not sure I follow you, but here's how I handle cell referencing.

Suppose I have an Excel file. In it are several worksheets, each with a name as shown on the tabs for each worksheet shown across the bottom of the file.

Suppose I have a worksheet named cat and another named dog.

Suppose cell A1 of cat contains 44 and I want that value to appear in cell A1 of dog.

I'd use this formula in A1 of dog:


The exclamation point forces dog A1 to look in cat A1 for the value. If you change the 44 in cat A1 to 99.32, the value shown in dog A1 will change to 99.32 as well.

Can't help with the other as I've never needed to do such a count.
My System SpecsSystem Spec
07 Apr 2014   #3

Linux Mint 17 Cinnamon | Win 7 Ult x64


For Q1. ignatzatsonic's answer is correct. One small caveat though. If you ever use a sheet name with a space you have to enclose the name in quotes.

Example: Sheet 1 is called "White Dogs" and sheet 2 is called "Black Cats".

The reference in White Dogs A1 would be ='Black Cats'!A1

Q2. There are several ways to count values in cells.

  • =COUNT(A2:A200) will count cells that contain numbers. Empty cells are not considered to be numbers. (
  • =COUNTA(A2:A200) will count cells that are not empty. That is, if it contains text, numbers or even a single space it will be counted. (For example is A2=27, A3=Fred and A4=' ' (a single space), and A4 to A200 were empty COUNT(A2:A200) would return a value of 3.
  • =COUNTBLANK(A2:A200) counts only empty cells. So if A2 and A3 had 10 and 20 respectively, COUNTBLANK(A2:A200) would return 197.
  • There are also COUNTIF and COUNTIFS, but these might be a little confusing for you at present. They count the number of cells depending on what's in them. You tell excell what to look for.

in excel if you click the fx beside the box where you type your formula excel will give you a chance to search for a function. If you type count number in column and press enter it will automatically select the count function for you.

What is a cell - has info on many excel functions.
My System SpecsSystem Spec


 *** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Insta

Thread Tools

Similar help and support threads
Thread Forum
BSOD referencing something with USB
HP laptop, WIn 7 Pro, 64 bit. Log attached. Thanks for any pointers on this. I have tried driver updates with DriverBooster3 to no avail.
BSOD Help and Support
Having problem setting cell height to fit text in cell
Very limited experience with Excel...have a multi page workbook but can't figure out how to auto format existing text to fit in cells so there is no space either on top or bottom of the cell. Only way I can get this so far is to manually move the bottom line of the text box up to the bottom of...
Microsoft Office
Can I compare 2 Excel spreadsheets cell by cell?
I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always ...
Microsoft Office
6 Cell Vs 9 Cell Battery for Laptop?
Someone needs to get a new battery for a very old laptop they have. The battery doesn't work so they have to have it plugged all the time otherwise it wouldn't work. Laptop is 15.6 and sort of heavy but the thing is they never take the laptop outside the house. It will mainly be used on one floor...
Hardware & Devices
BSOD referencing iaStor.sys. Running Win 7, 64-bit.
Hello! Running an HP Pavilion dv6 laptop w/Windows 7 Home Premium (x64). I have been battling an iaStor.sys BSOD for about 3 weeks now. No specific action seems to trigger it, from what I can tell. Strange that it seems to happen more frequently when my computer is connected to ac outlet...
BSOD Help and Support

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 08:24.
Twitter Facebook Google+