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

[winbroken]

*** ~ 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 ~ ***

 

[ignatzatsonic]

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.

 

[TanyaC]

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/excel2010functions/qt/2010-04-01-excel-2010-count-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