• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

Check for tab in another workbook

Hi, if i have a WB in path in

R:\XYZ\BORRIS INFO\CURRENT.XLS

Can i have formula in a cell which contains a an IF, asking, IF current.xls contains tab "Combined Data", "You have not saved file", ""

Thanks
0
Seamus2626
Asked:
Seamus2626
  • 7
  • 4
1 Solution
 
Seamus2626Author Commented:
PS i am checking this from another wb called xyz.xls
0
 
Rob HensonFinance AnalystCommented:
In WB xyz.xls Create a simple link to a sheet that does exist in the Current.xls file, the syntax should be:

R:\FilePath\'[FileName.xls]Sheetname'!CellReference

When creating this it will work because Sheetname exists.

Change the part that refers to SheetName to "Combine Data" (without the quotes) and a pop up will appear asking for the file location, click Cancel and the formula will be created but will give an error.

With that existing formula enclose it within an IFERROR function with the required message as the false parameter:

=IFERROR(Formula,"Error Message")

Thanks
Rob H
0
 
Seamus2626Author Commented:
problem here Rob, is that when i process my file and Combined Data dissapears, my error message turns to

=IFERROR(('X:\YOK\Boris Info\ME_Voris_Project\ME Downloads\[CurrentMonth.xlsx]#REF'!$A$1),"New Data saved")

Maybe i could use code and a button?

Thanks
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Rob HensonFinance AnalystCommented:
Well that is a change to the original question.

Yes you could use VBA to check if the other workbook includes a sheet called Combined Data.

The code would attempt to select the sheet in the other Workbook and if it produces an error rather than throwing you out of the code it returns an error message.

Thanks
Rob H
0
 
Seamus2626Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Seamus2626's comment #a40229137

for the following reason:

Il repost the VBA Q
0
 
Rob HensonFinance AnalystCommented:
The question was answered as originally worded.
0
 
Seamus2626Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Seamus2626's comment #a40229418

for the following reason:

Sorry! That was meant to be award points!
0
 
Seamus2626Author Commented:
Trying to award points
0
 
Rob HensonFinance AnalystCommented:
When raising the VBA question, include a link to this question.

About to travel for the rest of the day so may not see the VBA question come in, but if I do I will see if I can make a suggestion.

Thanks
Rob H
0
 
Seamus2626Author Commented:
Okay, i see, the VBA q was answered quickly by Randy

Thanks Rob
0
 
Seamus2626Author Commented:
Please award Rob 500 points
Rob Henson2014-07-30 at 09:18:20ID: 40229509

http:#40229509

Thanks
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now