Solved

Check for tab in another workbook

Posted on 2014-07-30
13
91 Views
Last Modified: 2014-08-18
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
Comment
Question by:Seamus2626
  • 7
  • 4
13 Comments
 

Author Comment

by:Seamus2626
Comment Utility
PS i am checking this from another wb called xyz.xls
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
 

Author Comment

by:Seamus2626
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Comment

by:Seamus2626
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
The question was answered as originally worded.
0
 

Author Comment

by:Seamus2626
Comment Utility
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
 

Author Comment

by:Seamus2626
Comment Utility
Trying to award points
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Comment

by:Seamus2626
Comment Utility
Okay, i see, the VBA q was answered quickly by Randy

Thanks Rob
0
 

Author Comment

by:Seamus2626
Comment Utility
Please award Rob 500 points
Rob Henson2014-07-30 at 09:18:20ID: 40229509

http:#40229509

Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now