Retrieving Data from one Sheet into another maintaining integrity after row deletion

Question

Level 1

8 points

Retrieving Data from one Sheet into another maintaining integrity after row deletion

Hello,

I have a Program that I use in Excel and Numbers. It is a Seniority List. In "Sheet 2" I have Seniority Date, Score, Scramble, Last Name and First Name. "Sheet 2" is a list of approx. 400 people. So, it's 5 Columns and 400 Rows. I have "Sheet 1" retrieving it's data from "Sheet 2". The problem is that when I delete a Row from "Sheet 2", I get an error or data loss in "Sheet 1". I've tried a combination of INDEX & MATCH, INDIRECT & ADDRESS, Sheet 2::Table 1::$B$26 to no eval. I've been trying a bunch different Syntax options and no luck. I cannot believe this is such an issue. How can I do this so that deleting a Row in "Sheet 2" without getting data loss or errors?

Thank You in advance!

MacBook Air 13″, macOS 12.7

Posted on Oct 24, 2024 2:19 PM

Reply

Answer 1

SGIII

Level 8

36,020 points

Oct 25, 2024 1:36 AM in response to Joeponcho

Joeponcho wrote:

I have "Sheet 1" retrieving it's data from "Sheet 2".

You've described Table 2 but what does Table 1 look like? What data are you retrieving?

Have you considered just using a filter on Table 2 instead of fiddling with formulas?

Filter data in Numbers on Mac - Apple Support

A screenshot would help visualize the specifics of what you are trying to do: shift-command-4, drag to select area, release, start new post here and use the mountains-and-moon icon beneath the compose window to insert the screenshot from the Desktop.

SG

Reply

Answer 2

SGIII

Level 8

36,020 points

Oct 25, 2024 8:56 AM in response to Joeponcho

You mention three screenshots, but you seem to have only two in your post.

What is a "Seniority Sheet"? Is it just what's in the first table ordered by date? If so, why not just sort on that column? Sorting is easy for novice users.

Alphabetize or sort table data in Numbers on Mac - Apple Support

BTW, if you aren't doing so already you should define the first row in the table as a Header Row. (Click the table and in the menu choose Table > Header Rows > 1).

If you are new to Numbers be sure to have a look at Help > Numbers Help and at the templates at File > New.

SG

Reply

Answer 3

SGIII

Level 8

36,020 points

Oct 26, 2024 3:20 AM in response to Joeponcho

I'm not sure. I think you will find addressing a need like this a lot easier if you learn about sort:

Alphabetize or sort table data in Numbers on Mac - Apple Support

And filters:

Filter data in Numbers on Mac - Apple Support

SG

Reply

Answer 4

Joeponcho Author

Level 1

8 points

Oct 25, 2024 4:31 AM in response to SGIII

This first Screenshot is my Feeder Sheet.

This Second Screenshot is the Seniority List that populates from the Feeder Sheet.

This Third Screenshot is the Overtime Signup Sheet which is just printed for people to signup on. It also populates from the Feeder Sheet.

The idea is that I only want the user (They are Novice and know nothing about programming) to just add and delete names from the Feeder Sheet and the other Sheets just populate. My problem is that anytime I delete a Name from the Feeder Sheet, the other two Sheets end up with #REF errors or data loss.

Reply

Answer 5

Joeponcho Author

Level 1

8 points

Oct 25, 2024 9:37 AM in response to Joeponcho

Here's the Third Screenshot. This is a Signup Sheet to be printed for people to signup. I'm trying to maintain the formatting in this sheet. This Screenshot has fictitious names but it grabs the Names from the Sheet called "SenList Feeder Alpha".

This is a more accurate Screenshot of the Seniority List. This is half of the first page of the Seniority List. I want to keep all the formatting of these two Screenshots (SignUp Sheet and Seniority List) and I want the user to only make changes to the SenList Feeder Sheet.

Shouldn't I just be able to put a formula in Cell B2 of the "Seniority List" that gets what's in Cell A1 of "SenList Feeder" somehow without mixing up the data in "Seniority List" when deleting a Row in "Seniority List Feeder" ?

Reply

Answer 6

Joeponcho Author

Level 1

8 points

Oct 26, 2024 4:57 AM in response to SGIII

Thanks SGIII, I'll read that but I think the issue is that as people Retire or Transfer, the User needs to delete a Person. From all the reading I've done, this sounds like a common problem that I'm not sure there's a solution for. I can't believe that as powerful as Excel & Numbers are that these Programs can't handle this.

Reply