Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2012
    Posts
    49
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 ~ ***
    Last edited by computerbabyproblems; 2014-04-06 at 23:56.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    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. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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
  •