I have an old \"checkbook\" spread sheet that I would like to modify. It looks l

Question

I have an old "checkbook" spread sheet that I would like to modify. It looks like this:

The formulas that summarize expenses and income look like this

B31 is the cell that has "SAL" in it. TABLEBASE defines the entries above the table. I don't remember how I did this, but, obviously, it was based on DSUM. I want to modify the table to add additional categories, but can't find any way to modify it. Excel acts like it knows this is a table because anything I try usually ends up with an error "cannot modify part of a table." In the formula bar, the formulas look like {=TABLE,B31)} but as soon as you select the formula bar, the {} disappears and if I hit the return key, I get an error. Also, I cannot find a way to duplicate this in a vertical fashion. It can be done horizontally by

where B60:B61 is CAT/SAL, C60:C61 is CAT/TAX, ettc.

So what does the {=TABLE,B31)} do, and how can I hadd more rows to the table????

DATE # EXPENSE X INCOME DESCRIPTION CAT 1000 SAL 50 TAX 300 MORT 150 REC 40 MINC 200 GROC 100 GROC 200 REC 30 GROC 1030.00 1040.00 TOTALS 10.00 BALANCE 10.00 CATEGORY BALANCE (SET TABLEBASE) 330 HEDY'S CATEGORIES X X 0.00 0.00 0.00 X BALANCE CAT CATEGORY SAL 0.00 1000.00 SALARY RET 0.00 0 0.00 RETIREMENT INS 0.00 0 0.00 INSURANCE TAX 50.00 0 0.00 TAXES SAV 0.00 0 0.00 SAVINGS MINC 0.00 0 40.00 MISCELLANEOUS INCOME GROC 330.00 0 0.00 GROCERY EO 0.00 0 0.00 EATING OUT GAS 0.00 0 0.00 GASOLINE AUTO 0.00 0 0.00 AUTOMOBILE MAINTENANCE CAB 0.00 0 0.00 CABIN CLOTH 0.00 0 0.00 CLOTHING BEAUT 0.00 0 0.00 BEAUTY MED 0.00 0 0.00 MEDICAL MORT 300.00 0 0.00 MORTGAGE UTIL 0.00 0 0.00 UTILITIES HOUSE 0.00 0 0.00 HOUSE MAINTENANCE FIN 0.00 0 0.00 FINANCIAL CC 0.00 0 0.00 CREDIT CARD PAYMENTS CHG 0.00 0 0.00 CREDIT CARD CHARGES CONT 0.00 0 0.00 CONTRIBUTIONS GIFT 0.00 0 0.00 GIFTS PROF 0.00 0 0.00 PROFESSIONAL TRVL 0.00 0 0.00 TRAVEL REC 350.00 0 0.00 RECREATION GRAN 0.00 0 0.00 GRANNEN MISC 0.00 0 0.00 MISCELLANEOUS UNACT 0.00 0 0.00 UNACCOUNTED

Explanation / Answer

The Table function is a special array function that returns multiple values. It can be entered (or modified) only by using the Data Table dialog box, which you can reach in Mac Excel 2011 via menu Data > Data Table or ribbon Data > What-If > Data Table.

Your example =TABLE(,B31) returns values in a column (since the Row Input Cell argument is missing and B31 is the Column Input Cell). The values that are returned depend on the formula DSUM at the top of the column when each value in a cell to the left of the TABLE column is substituted into cell B31.

To modify, you must select the entire table and additional rows (or columns), and then use the Data Table dialog box described above.

To see the entire table range in an existing data table, select a cell in the data table, and choose menu Edit > Go To > Special > Current Array.

For more info, choose Help > Excel Help > Data Analysis > What-If Analysis > Caalculate multiple results by using a data table > Create a one-variable dat~a tablee.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.