Data refresh question

[HeRoseInThree]

I have a spreadsheet that has my driver's names (and other data) and another spreadsheet that I use that data for.

When a driver is removed from the driver data spreadsheet, I receive an error on the other sheet...

The formula works well until I remove a driver.

The formula I use:
='[N&I Driver Data.xlsx]Driver Data'!D53
If I remove the driver in D53 I receive a #REF ! error

ANY help would be appreciated!!!

 

[Tom Urtis]

A useful piece of information would be to explain what you mean by this:
"When a driver is removed from the driver data spreadsheet"
My suspicion is that your meaning of "removed" means something more than the contents of cell D53 being cleared. a REF error usually occurs when a reference to a cell or range location is illogical, such as maybe if a row or column was actually deleted instead of its contents cleared. The formula under normal circumstances would otherwise return a null string (not an error) because an empty cell that contains nothing would return nothing, which is a common enough formula result.

My other question is, what the heck kind of avatar is that you have going on? Reminds me of my high school math teacher from years back after a midterm exam.

 

[HeRoseInThree]

When I remove a driver (I'm dealing with truck drivers and they come and go often) so, removing the driver's data as they are no longer employed with us.


My avatar is a character from Clash of Clans, but I appreciate the math teacher reference. I had a coach or two look like that

 

[Tom Urtis]

I'm glad I asked about REF error; I assumed your use of "driver" meant a hardware driver that communicates with your operating system. OK, so you mean "driver" as in the operator of a motor vehicle.

The REF error is occurring because Driver Data as the N&I workbook name, or sheet name or cell D53 of that sheet in that workbook does not exist. It's a safe bet that cell D53 ***does*** exist on the sheet so why you are getting that REF error must be because something else is going on with your workbook that you either do not know about or are not saying.

In any case, to address your immediate concern, depending on what you want the formula to return or to accomplish, this for starters might be what you want. If it isn't what result you want to achieve, you'd need to supply more information about what you are doing.

=IFERROR('[N&I Driver Data.xlsx]Driver Data'!D53,"Can't get there from here")

 

[HeRoseInThree]

Yes, driver as in truck driver. I never considered software driver : ) Sorry

If a driver leaves our organization, I delete their info on the driver data sheet. But, on another sheet that draws from the driver data, it then gives me the error.

 

[Tom Urtis]

That gets us back to my point about what else is going on in the workbook. The way you...
• have your other sheet designed to draw driver data
• and/or how and why you delete a driver's info
• and/or why, when you know a driver's data will be deleted, your calculation does not take that possibility into account (which the IFERROR example would)...
is/are the crux(es) of the issue.

Bottom line, if the basic IFERROR function does not work for you, which I assume it does not because you didn't say that it does, then there needs to be a further explanation of how these other sheets and processes are set up.

 

[HeRoseInThree]

That gets us back to my point about what else is going on in the workbook. The way you...
• have your other sheet designed to draw driver data
• and/or how and why you delete a driver's info
• and/or why, when you know a driver's data will be deleted, your calculation does not take that possibility into account (which the IFERROR example would)...
is/are the crux(es) of the issue.

Bottom line, if the basic IFERROR function does not work for you, which I assume it does not because you didn't say that it does, then there needs to be a further explanation of how these other sheets and processes are set up.

I have thought about this for a while and have no idea as to how to say it a different way.

My other sheets draw data the same way "='[N&I Driver Data.xlsx]Driver Data'!D52" except the error "='[N&I Driver Data.xlsx]Driver Data'!#REF!"

I just delete the driver's data when they leave. This answers the how and why.

I know when a driver's data will be deleted when they leave.

It seems like a refresh issue, but I dunno.

I appreciate you trying to help, but I don't know how to rephrase the issue.

 

[Tom Urtis]

Try this, assuming the N&I Driver Data.xlsx workbook is open at the same time because INDIRECT does not work when referencing a closed workbook.

=INDIRECT("'[N&I Driver Data.xlsx]Driver Data'!D52")

 

[HeRoseInThree]

THAT DID THE TRICK!!!!!

Thanks!!!

 

[HeRoseInThree]

Follow up question. How did you insert the code: "picture"?