Absolute reference to a cell of another table

datatronics505

Board Regular
Joined
Nov 26, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
For the purpose of following an online course on accounting, I have created two tables: one is a daily journal where all transactions are recorded and the other is a T-account that reflects changes of an individual account that was previously listed in that journal. Both are formatted as tables, columns that need to contain only text are formatted as text, those that contain dollar amounts are formatted accordingly and columns containing dates are formatted for dates.

As presented in the screenshot, there's only one T-account (the table on the right), but as the journal grows, so will the number of those small tables.

I am trying to reference specific cells from the daily journal into the cells of the smaller T-account table in order to avoid re-typing the same data. For example, both the daily journal and the T-account tables have the Date column. I am referencing the specific Transaction date from the daily journal into the Date cell of the appropriate T-account table by clicking on the Date cell first, typing "=" followed by clicking on the Transaction date cell and pressing TAB. Results however are unpredictable. I will get the reference to the date right but then when trying to reference the account name (cells at both tables are formatted as text) I am getting the literal text describing the reference and not the text value that I want to see in the cell.

Furthermore, when pressing TAB when done entering data into the last cell in the row of the T-account table, the new row immediately references the next date in the Daily journal, something I don't want.

Material found online about referencing data from one table to another mentions referencing rows and columns entirely, and not individual cells. How would I address particular cells only from the Daily journal table into individual T-account tables? Preferably as an absolute reference.
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
If it helps, here's a screenshot:
 

Attachments

  • ScreenshotDailyJournal.png
    ScreenshotDailyJournal.png
    12.5 KB · Views: 10
Upvote 0
I suspect your formula cell is formatted as Text, which is why you see the formula as entered, not calculated. Format the cell as anything but Text, then just press f2 to enter edit mode and Enter.
 
Upvote 0
I suspect your formula cell is formatted as Text, which is why you see the formula as entered, not calculated. Format the cell as anything but Text, then just press f2 to enter edit mode and Enter.
Formatting the cell as General alone helped get the reference right, F2 + Enter was not needed. But then when I press TAB to get to the next line of the T-account, the whole next line of Daily Journal is repeated into the T-account table, which I don't want- with ranges not formatted as tables what I wanted was achieved with absolute references. Is it really that tables can't handle absolute references?
 
Upvote 0
Tables are designed to repeat formulas down all their rows, and there isn't really a table 'absolute row' syntax (there is one for columns but it's hideous). Why do you want a table at all for this?
 
Upvote 0
Solution
Tables are designed to repeat formulas down all their rows, and there isn't really a table 'absolute row' syntax (there is one for columns but it's hideous). Why do you want a table at all for this?
My perception of tables as the better way of keeping records in Excel, just a perception- that's why I went for tables.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,344
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top