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

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

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 this function in excel much.. googling is very messy, confusing, and scattered.

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

2. Easy, I put the number 100 in cell A1 on Sheet1. Next on Sheet2 I entered (without the quotes) "=100-" and then, without hitting the return key, I browsed back to sheet1 and clicked on cell a1 and then pressed enter. Excel created the formula "=100-Sheet1!A4"

2nd, to count the number of non-blank cells in a range enter (I used a range of A1 to A35 for this example) "=COUNTA(A1:A35)"

This will count the cells in the range that aren't blank. Note that a cell with an apostrophe in it is not counted as blank, even though you can't see the character unless you select the cell.

Also you didn't mention which version of Excel you were using, so I didn't explain how to use the function tool so you could see all the options you had.

Doug

3. 1) Sheet 2 cell A1 needs the formula =100-Sheet1!A1
2) Instances of anything =COUNTA(A2:A200)
Instances of numbers =COUNTIF(A2:A200,1)
Instances of a string =COUNTIF(A2:A200,"apple")
Instances of True =COUNTIF(A2:A200,True)

HTH,
Maud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•