Referencing Data from Another Sheet in a Formula

Jack220191

New member
Joined
Jun 3, 2016
Messages
3
Reaction score
0
Points
0
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
 
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.
 
Thanks. I think I have attached it to this message...
 

Attachments

  • Excel with Business Workbook 1.xlsx
    10.4 KB · Views: 6
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.
 
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
 
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.
 
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! :)
 
Back
Top