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 SP1, Home Premium, 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 for2: *** ~ 1) Basic Referencing Cell From Another Sheet & 2) Counting Insta
Thread Forum
Upgrading from Home Premium to Ultime without having to do clean insta Installation & Setup
Solved BSOD referencing 0x00000116 and dxgkrnl.sys after replacing most hw BSOD Help and Support
Windows 7 Ultimate x64 error 800B0100 referencing KB976933 Windows Updates & Activation
Random BSOD / Insta boot. BSOD Help and Support
4 Frisbees and Counting... Sound & Audio

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 09:16.
Twitter Facebook Google+

Windows 7 Forums

Seven Forums Android App Seven Forums IOS App