Use a single Cell to fill cells in a column - Budget inputs weekly

Question

Use a single Cell to fill cells in a column - Budget inputs weekly

I want to use a single cell (input paycheck here) to add my paycheck to my table every 2 weeks.

Each payday should populate in the NEXT empty cell in the AMT column. The Pay amount will vary. Changing the input cell amount should not change the previous cell and should continue to populate the column thru the year. So pay for 1/13/23 should "Stick" once inputed (is that a word?).

Then, I would "hide" Table 6 (on another sheet of background tables) as I have created various charts to display budget items and clean up the sheet visually. Trying to create a sleek, simple, and visual budget sheet and this is one of my roadblocks right now....Is this even possible?

Posted on Nov 19, 2022 8:28 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,300 points

Nov 19, 2022 10:18 AM in response to christopherfromcollinsville

I'll post this idea but, personally, it would be so much easier and more robust to simply type the paycheck amount into the correct row of the table vs setting all this up.

Another, probably better, idea would be to use an AppleScript to transfer the amount from the one table to the other. It would be saved as a service or "quick action" or shortcut. Instead of hitting "Return" after entering the amount, you would use some other key combination. Maybe someone else can write something up for you if I don't get to it. I can't do it right now.

Table 1::B2 =IF(Table 2::A≠"",Table 2::A,"")

Fill down to complete the column.

B2 will get its value from Table 2::A2, B3 will get its from Table 2::A3, etc.

Make Table 2 as long as or longer than Table 1.

Table 2::B2 =IF(AND(A2="",A1≠""),"Show","")

Fill down to complete the column

This formula will put the word "Show" in the first available blank row.

Set up the filter as shown for Table 2 column B so it will show only the row that has "show" in it. I have the filter turned off in the screenshot.

When it is all set up, hide column B

If you make an error in your paycheck entry, I suppose you could overwrite the formula in Table 1 with the correct number. Or you could turn off the filter on Table 2 and make the correction there.

Reply

Answer 2

Nov 19, 2022 8:52 AM in response to christopherfromcollinsville

I want to use a single cell (input paycheck here) to add my paycheck to my table every 2 weeks.

Each pay amount should populate in the next empty cell in the AMT column.
The pay amount will vary each time.
Changing the input cell amount should not change the previous cell and should continue to populate the column thru the year. So pay for 1/13/23 should "stick" once inputed (is that a word?) and not change as I input 1/27/23's amt.

I will "hide" Table 6 (on another sheet of background tables) as I have created various charts to display budget items and clean up the main sheet visually. Trying to create a sleek, simple, and visual budget sheet and this is one of my roadblocks right now....

Is this even possible?

FYI these tables have no formulas currently... this is an example

Reply

Answer 3

Badunit

Level 7

28,300 points

Nov 19, 2022 9:46 AM in response to christopherfromcollinsville

Numbers for iOS has "forms" that will do basically what you are asking. Numbers for Mac OS does not. You can fake it so it looks like it, though. The "input paycheck here" table will actually be an entire column but only one row/cell will be shown at a time. When you make an entry it hides that row and shows you the next empty row. I'll mock up an example to show how it can be done.

Reply

Answer 4

Nov 20, 2022 8:05 AM in response to Badunit

Thanks so much! Great solution and not too complex. This will help save on real estate on the budget sheet so I can have some inputs and see some graphs of what is going on without the tables taking up so much area.

Curious about the IOS version - hope that functionality makes it to the Mac!

Thank you for the quick response!

Chris

Reply

Answer 5

Barry

Level 9

59,951 points

Nov 20, 2022 9:51 AM in response to christopherfromcollinsville

"Curious about the IOS version - hope that functionality makes it to the Mac!"

Forms were likely added to the iOS version due to the minimal acreage available on an iPhone or iPad, compared to comparatively vast space available on even the smaller screen Mac models.

If you do have a need for Forms in Numbers for Mac, use the Provide Numbers Feedback menu item in the Numbers menu to make a feature request directly to Apple.

Regards,

Barry

Reply

Use a single Cell to fill cells in a column - Budget inputs weekly

Similar questions