Results 1 to 7 of 7

Thread: Referencing Data from Another Sheet in a Formula

  1. #1

    Referencing Data from Another Sheet in a Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I'm undertaking an online introductory Excel course as I'm looking at moving into a career in accounting.

    I'm quite early on in the course however have already encountered an issue that I don't know how to work around. It's with regards to referencing data from a cell in another sheet. In this particular example the sheet on which I am entering the formula is titled "Sales" and the sheet from which I want to reference the data is titled "Data". Cell D4 in the Sales sheet contains the net sales value of a product and cell B1 of the Data sheet contains the sales tax rate (20%). To work out the value of the sales tax I enter the following formula into cell E4 on the Sales sheet; =D4*Data!B1. This is what the tutorial says you should do and what everything else I've found online says you should do. For some reason every time I try and do this it instantly refers to the data in cell B1 on the Sales sheet which is the cell that contains today's date (=TODAY()), and as a result doesn't provide me with value that I want (it keeps coming up in date format).

    I was wondering if anyone else has ever encountered a similar problem and knew how to work around it? It happens both when I select cell B1 in the Data sheet and manually enter "Data!B1" into the formula bar of the Sales sheet. In fact when I do the former as soon as I switch back to the Sales sheet it shows up as "Sales!B1". Needless to say this is very annoying, any advice would be much appreciated (I'm using Excel 2013 BTW).

    Many thanks

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Not encountered the problem myself - it may have to do with how you are trying to set up the formula, however attach the sheet here and someone can take a look for you.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Thanks. I think I have attached it to this message...
    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    Try reformatting the column on the Sales sheet to General or Number - it's currently defaulting to date format. Get the formatting right and your formula will work.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Thanks! That seems to have worked If I try selecting the cell in the Data sheet it still automatically changes to the corresponding cell in the Sales sheet when I switch back, but it does the trick when I enter manually

    Many thanks

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,435
    Articles
    0
    Excel Version
    Office 365 Subscription
    I don't understand what you are saying here. The date shown in the column on the Sales sheet is NOT being read form the Sales sheet: it is the result of the formula presented as a date, that's all. It doesn't matter whether I type the whole formula or I select cell B1 on the Data sheet whilst setting it up, as long as the cell on the Sales sheet is correctly formatted, the result shows as it should.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Jack
    The problem is that just cell E4 on the Sales tab is formatted as a date. I tried some of the other cells in Col E and these show the correct value. If you format a cell as a date then Excel is quite happy to express any number in there as a date!

Tags for this Thread

Posting Permissions

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