Forum Discussion
Enter formula in Excell table cell
I have an excel table and want to have a formula in the cell that depends on data in the same row as well as data in the row above. When I input:
= C3+if([@[Full Text]]=E3,0,1)
The cell displays the formula and not the value of the formula. If I enter the formula in a cell outside the table,
=A3+IF(AnalysisTable[@[Full Text]]=E3,0,1)
it works just fine.
The table header begins at C2
How do I get a formula to calculate using cell references in addition to table references?
Thanks,
BarryD97
3 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Hi Hans--
Here is a link to a sample workbook (https://1drv.ms/x/s!ApdC8bCEGKkjh9EPH4canb316oSZ8w?e=Hnqpi3). It has two sheets: "Copy" is a copy of the sheet from my original workbook with the sensitive data replaced and then an additional range added to calculate the House ID. "Test" is new sheet with the same data all manually entered and then a copy made and converted into a table.
The table in the "Copy" sheet still shows the problem while the table in the test sheet does not.
It looks like there is some property of the original table that prevents its copy from acting properly.
Any help would be appreciated.
Barry
Thanks!
The cells in the HouseID column on the Copy sheet have been formatted as Text, while those in the same column on the Test sheet are formatted as General.
If you enter a formula in a cell formatted as Text, it will remain just a piece of text instead of operating as a formula. So:
- Format the HouseID column as General.
- Redo the formula in B4.
- Fill down.
