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
winbroken

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
ignatzatsonic

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:

=cat!a1

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
TanyaC

Linux Mint 17 Cinnamon | Win 7 Ult x64
 
 

Hi,

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. (http://spreadsheets.about.com/od/exc...t-function.htm)
  • =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 - http://www.ask.com/question/what-is-a-cell-in-excel

http://spreadsheets.about.com has info on many excel functions.
hth
Tanya
My System SpecsSystem Spec
.

Reply

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




Thread Tools




Similar help and support threads
Thread Forum
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
BSOD referencing 0x00000116 and dxgkrnl.sys after replacing most hw
Hello, I am completely stumped with my recent computer issues. Let me give a little back story of what I know of this particular error. Back in January I was experiencing terrible issues while playing games like Sleeping Dogs and Far Cry 3 where my GPU would increase upwards of 100+ C and...
BSOD Help and Support
Windows 7 Ultimate x64 error 800B0100 referencing KB976933
Figured i would post here and see if anyone has any ideas. I have tried numerous methods to get this to install, using CheckSUR as well as many others even spent 4 hours with a microsoft rep yesterday to no avail. I have Included both the CheckSur log and CBS log. I have tried both the automatic...
Windows Updates & Activation
Random BSOD / Insta boot.
Is Windows 7 . . . x64 full retail version - What is the age of system (hardware)? = 1 week - What is the age of OS installation = 2 days works fine at desktop ..then whislt gaming crashes whilst playing. Im now the proud owner for $500 worth of ram (2 different mhz / voltage types)...
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 05:33.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App