Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office

When using the INDIRECT function I encounter a circular

Customer Question
reference that I cannot uncover...
When using the INDIRECT function I encounter a circular reference that I cannot uncover. I am trying to drop a file name into a cell in order to pull information from another worksheet.
JA: What kind of computer do you have?
Customer: MAC
JA: Have you installed any updates recently?
Customer: This is the formula I am using =IF(INDIRECT("'["&D$82&".xls]"&"Sheet1'!$B19")=0,"",INDIRECT("'["&D$82&".xls]"&"Sheet1'!$B19"))
JA: Anything else you want the microsoft office expert to know before I connect you?
Customer: that is it
Submitted: 1 year ago.Category: Microsoft Office
Show More
Show Less
Ask Your Own Microsoft Office Question
Answered in 27 minutes by:
11/15/2016
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago
The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 2,084
Experience: 20 years experience providing remote computer support
Verified

please send example file.

and please explain what you are trying to do.

It gets very picky trying to indirect another workbook.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I have another workbook that I need to drive information from The information will change workbook to workbook. So I am inserting a file name into the cell that needs to pull the information from the workbooks. That file name is ***** ***** the team I am looking for. There are over 350 teams and 350 workbooks. I am able to have excell look up the worksheet that I desire, but a circular reference exists. The worksheet is responding as I need, it is just this circular reference that I can't seem to eradicate.You will see that the information is being pulled correctly. My problem is not getting the information into the spreadsheet, it is the circular reference that exists.
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

which of the workbooks you provided is the circular reference one, and which cell is the formula in?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
test.xls
Customer reply replied 1 year ago
The circular reference comes from the names in column A.It begins with A40 and moves upwards.
Customer reply replied 1 year ago
I am sure you already know that you need all files open for excel to bring forth the desired information.
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

i need playerstats.xls to test

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

there is a link to that file

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Ok....Here it is
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

ok, so what is happening is you are using the value in column A to vlookup and populate the value in column C. And then the indirect reference in column A references back to column C. Do you see the problem?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
let me look....
Customer reply replied 1 year ago
but they are different rows?
Customer reply replied 1 year ago
Are you saying if I move A 23 to B23 and then have C23 look into B23 instead of A23 the circular reference will go away?
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

ok, your right

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

let me check something else

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
ok
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

ok, i am not getting a cicular error, I am getting something totally different

first things first the file names are ***** ***** upper case, and you do not have upper case in the cell that is refered to in the formula, if you use the UPPER() function in your formula for cell c1, c23 and so on. or change the file names so they match. that should help.

Let me know if that fixes anything

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I changed C1 and C23 to be upper case. This is the file name that excel is inserting into the A3:A18 and c25:c40.This did not fix anything.
Customer reply replied 1 year ago
The files that I sent to you have circular references. Not sure how they went away when I transferred them to you?The cell with the circular reference is A40.
Customer reply replied 1 year ago
What are you getting if not a circular reference?
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

this is what I am getting.

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

am i supposed to be getting something different.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Whether the file is in uppercase or lowercase doesn't seem to be the problem. Mainly because the worksheet is accessing the correct information. The problem is because of the circular reference. I don't understand why I am getting a circular reference because no two cells are accessing each other's information. Is this something that you can fix or help me with or is it beyond your grasp? If you only have Excel 2010, how can you hold yourself out to be a PC expert? Do you have another person who has a more current version of excel?
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

i don't think the version is at issue.

It may be your OS and your excel.

We may need to set up a remote session.

But if you want to work with someone else that is fine, they are just going to tell you the same thing.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I do not want to work with someone else. I just need to know whether this is something you can handle. If you believe it is, then let's fix it together.
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

yes I can handle it if I can see the error. lool, this is why I reccomend remote.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Okay give me a few minutes to clean up my workspace and then we can connect remotely will you send me a link to do that?
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

teamviewer i believe there is a mac version

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I have already paid $37 why are you charging me additional? I don't think it's fair since you have not solve my problem. I am looking to do and membership monthly fee but I do not want to be continually hit with charges like this
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

sorry the site always charges extra for remote support.

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

i may just opt out, and let someone else take over. Since you don't seam confortable with this option

Ask Your Own Microsoft Office Question
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

and I wish i could recreate the error, but I can't so that only leaves one option. remote.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
This is a very high-level problem. As you can see. I don't like spending money without getting a completed answer. I've already been charged $37 and I don't want to pay more without knowing I'm going to get a resolution. Do you have somebody that you feel more comfortable that can solve my problem. Without having to go remote you might have a more current version of excel?
Microsoft Office Technician: The-PC-Guy, Computer Manager replied 1 year ago

version, is not the issue.

but I am going to open your question to the pool of people here, because we do not seam to be on the same page here, and there is no point in spending any more time if you don't wish to pay for the remote service.

someone should be along soon

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Thanks. I appreciate your efforts.
Customer reply replied 1 year ago
Nobody has tried to contact me yet. Can you please find out when I should expect a contact?thank you
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago
bbao
bbao, IT Consultant
Category: Microsoft Office
Satisfied Customers: 856
Experience: CISM / CISSP / MCSD - Azure Solutions Architect / MCSE - Cloud Platform and Infrastructure / MS MVP
Verified

Hello, this is Bing and I am reviewing your question.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Hi Bing. I hope you can help me. It is kind of a high level issue. Do you have the files?
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Just downloaded. Please bear with me for a while because I got a meeting in 20 mins so I can't be available very soon. Will advise once all files have been reviewed. Thanks.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
thanks...will be awaiting your reply
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

You are welcome.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Did you forget about me?
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

NO! But I am sorry still in meetings away from my Mac. I will review your questions once get back to my Mac. No worries.

Ask Your Own Microsoft Office Question
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Or, if you do prefer, I may opt out for now. If I am available again and no other experts working on it, I will opt in again. Be aware that the question value may increase a bit this way. Is it okay for you?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I just want someone to help solve my issue. I have already paid for your expert advise. I would like to receive it.
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Sorry again. I will finalize your question in two hours. Is it convenient for you today?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
I cannot see the bottom of the page
Customer reply replied 1 year ago
the bottom of my page says circular A40
Customer reply replied 1 year ago
no it does not appear on your page. Wonder why not?
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

What's your Excel version? I am using Excel for Mac 2011. I also cross verified your spreadsheet with Excel 2010 on Windows 7. The same thing.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Microsoft Excel for Mac 2011Version 14.5.0
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Mine is 14.6.9, a bit higher but I don't think it makes sense.

I reckon it is caused by settings. Bear with me for a moment.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
thank you so much.
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

The issue is in Column B, not A.

Ask Your Own Microsoft Office Question
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

> =IF(INDIRECT("'["&D$82&".xls]"&"Sheet1'!$B19")=0,"",INDIRECT("'["&D$82&".xls]"&"Sheet1'!$B19"))

What do you expect to get $C$82 or $D$82?

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
The numbers I am drawing in are from another worksheet. Two to be exact. This spreadsheet is used to pull in multiple worksheets depending upon the file I input.So $c$82 is inserted into the name of the worksheet. I don't use $d$82
Customer reply replied 1 year ago
By the way, the information that is pulled in is correct. It is just the circular problem that I have.
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

FYI - There is no any errors on Excel 2010 at all. Please see attached the screenshot.

This does indicate a version difference issue, though initially I didn't think it could be a problem. Therefore I do suggest you to patch your Office for Mac to the latest version, then troubleshoot again and see if the error persists.

Ask Your Own Microsoft Office Question
Customer reply replied 1 year ago
Ok I'll try that
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

You know, it is not worth it spending hours on troubleshooting issues caused by a bug or defect of MS products. :-)

Ask Your Own Microsoft Office Question
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Any update please?

Ask Your Own Microsoft Office Question
Microsoft Office Technician: bbao, IT Consultant replied 1 year ago

Hello? Did you try updating your Office for Mac? There was a recent update last week.

How about the issue? No longer a problem?

Ask Your Own Microsoft Office Question
Ask bbao Your Own Question
bbao
bbao
bbao, IT Consultant
Category: Microsoft Office
Satisfied Customers: 856
856 Satisfied Customers
Experience: CISM / CISSP / MCSD - Azure Solutions Architect / MCSE - Cloud Platform and Infrastructure / MS MVP

bbao is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

701 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

2,084 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

157 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

147 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
I can't recover a really important document that I was
I can't recover a really important document that I was working on … read more
bbao
bbao
IT Consultant
856 satisfied customers
I'm having trouble with creating an automatic table of
I'm having trouble with creating an automatic table of contents in Word. I think it has something to do with formatting in the document. … read more
Kris R
Kris R
IT Manager
Computing
2,629 satisfied customers
When I type an e mail to a particular person, how do I
When I type an e mail to a particular person, how do I include multiple names recipients . … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
701 satisfied customers
I have a document on one drive that wont open because the
I have a document on one drive that wont open because the file is corrupted. How can I open the document. It represents a weeks work. Would it help to use a different computer? … read more
Kris R
Kris R
IT Manager
Computing
2,629 satisfied customers
We have Microsoft Word on our old computer under Wayne and
We have Microsoft Word on our old computer under Wayne and Beth Raasch Product ID 03***-***-****774-02692. When we tried to load it onto our new computer it asks for a 25 number long ID. This is all w… read more
Kris R
Kris R
IT Manager
Computing
2,629 satisfied customers
I just updated my Office package today, & now I'm missing
I just updated my Office package today, & now I'm missing the Frutiger font I purchased. It shows up in FontBook but not in the MS Word list. Most of my documents have a substituted font. How do I get… read more
bbao
bbao
IT Consultant
856 satisfied customers
I bought Word in 2013 for a lifetime, and a few weeks ago it
I bought Word in 2013 for a lifetime, and a few weeks ago it quit working. It won't open. … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
701 satisfied customers
New HP PC. Tried to share a file (failed). Unable to close
New HP PC. Tried to share a file (failed). Unable to close Word. Was using Yahoo mail. Just set up Outlook. Still unable to close Word. Get error about new message. Not sure what to do. … read more
Cris
Cris
58 satisfied customers
I have a word file of 542 mb. Word won't open it reason it's
Hello there. I have a word file of 542 mb. Word won't open it reason it's larger than 32 mb in size. (However it will open a 330.4 mb file, both of which contain images and text). … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
701 satisfied customers
CAN YOU HELP ME RETRIEVE MY PRODUCT CODE I JUST PURCHASE FOR
CAN YOU HELP ME RETRIEVE MY PRODUCT CODE I JUST PURCHASE FOR MY MICROSOFT OFFICE … read more
Moses C
Moses C
Bsc
332 satisfied customers
Is there a way to recover a document accidentally deleted
Is there a way to recover a document accidentally deleted from recycle bin? … read more
Chris L.
Chris L.
Support Specialist
Vocational, Technical or Trade Scho
147 satisfied customers
I recently re-subscribed to Office 365 as support for One
I recently re-subscribed to Office 365 as support for One Drive on my iPad so that I can use either the iPad or my Mac Book Pro for work. My problem is I can't find all the editing tools I need to typ… read more
bbao
bbao
IT Consultant
856 satisfied customers
I installed windows 10 update assistant and now I cannot
I installed windows 10 update assistant and now I cannot open Norton or office programs. click-2-run virtualisation handler keeps popping up. … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,275 satisfied customers
My computer crashed a couple a months since than on some
my computer crashed a couple a months since than on some documents I am getting "accessed denied contact administrator" how do I get access to the document … read more
Jason Jones
Jason Jones
AAS Information Technology
6,320 satisfied customers
I have created a memory book in Word with many photos. Most
I have created a memory book in Word with many photos. Most book publishers need it in a PDF. When I change it to a PDF there are shadows on 1/3 of the page and the typing and photos are smaller fitti… read more
George Sibiya
George Sibiya
PhD
216 satisfied customers
I am writing a paper on word 2006, think that is the
Hi, I am writing a paper on word 2006, think that is the edition...anyway when I look @ print preview on my mac the pages are short...that is the preview displays the 1sr, 2nd, 3ed, etc pages as only … read more
bbao
bbao
IT Consultant
856 satisfied customers
I got an error message saying something about my normal
Hi, I got an error message saying something about my normal template needing saving. I cancelled that and now none of my word documents will open. I'm on a Mac and running Office 365. … read more
Sheri
Sheri
Bachelor\u0027s Degree
94 satisfied customers
I can not open my skype account. No I did not rearranged my
No I did not rearranged my file folder, Microsoft did automatically … read more
bbao
bbao
IT Consultant
856 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x