How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask bbao Your Own Question
bbao
bbao, IT Consultant
Category: Microsoft Office
Satisfied Customers: 479
Experience:  CISM / CISSP / MCSD - Azure Solutions Architect / MCSE - Cloud Platform and Infrastructure / MS MVP
96068672
Type Your Microsoft Office Question Here...
bbao is online now
A new question is answered every 9 seconds

When using the INDIRECT function I encounter a circular

Customer Question

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: 8 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 8 months ago.

please send example file.

and please explain what you are trying to do.

It gets very picky trying to indirect another workbook.

Customer: replied 8 months 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.
Expert:  The-PC-Guy replied 8 months ago.

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

Customer: replied 8 months ago.
test.xls
Customer: replied 8 months ago.
The circular reference comes from the names in column A.It begins with A40 and moves upwards.
Customer: replied 8 months ago.
I am sure you already know that you need all files open for excel to bring forth the desired information.
Expert:  The-PC-Guy replied 8 months ago.

i need playerstats.xls to test

Expert:  The-PC-Guy replied 8 months ago.

there is a link to that file

Customer: replied 8 months ago.
Ok....Here it is
Expert:  The-PC-Guy replied 8 months 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?

Customer: replied 8 months ago.
let me look....
Customer: replied 8 months ago.
but they are different rows?
Customer: replied 8 months 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?
Expert:  The-PC-Guy replied 8 months ago.

ok, your right

Expert:  The-PC-Guy replied 8 months ago.

let me check something else

Customer: replied 8 months ago.
ok
Expert:  The-PC-Guy replied 8 months 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

Customer: replied 8 months 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: replied 8 months 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: replied 8 months ago.
What are you getting if not a circular reference?
Expert:  The-PC-Guy replied 8 months ago.

so maybe the files got renames when they were uploaded, maybe they got changed to upper case there,.

Im using excel 2010, so maybe it treats things a bit differently..

Expert:  The-PC-Guy replied 8 months ago.

this is what I am getting.

Expert:  The-PC-Guy replied 8 months ago.

am i supposed to be getting something different.

Customer: replied 8 months 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?
Expert:  The-PC-Guy replied 8 months 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.

Customer: replied 8 months 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.
Expert:  The-PC-Guy replied 8 months ago.

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

Customer: replied 8 months 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?
Expert:  The-PC-Guy replied 8 months ago.

teamviewer i believe there is a mac version

Customer: replied 8 months 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
Expert:  The-PC-Guy replied 8 months ago.

sorry the site always charges extra for remote support.

Expert:  The-PC-Guy replied 8 months ago.

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

Expert:  The-PC-Guy replied 8 months ago.

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

Customer: replied 8 months 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?
Expert:  The-PC-Guy replied 8 months 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

Customer: replied 8 months ago.
Thanks. I appreciate your efforts.
Customer: replied 8 months ago.
Nobody has tried to contact me yet. Can you please find out when I should expect a contact?thank you
Expert:  bbao replied 8 months ago.

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

Customer: replied 8 months ago.
Hi Bing. I hope you can help me. It is kind of a high level issue. Do you have the files?
Expert:  bbao replied 8 months 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.

Customer: replied 8 months ago.
thanks...will be awaiting your reply
Expert:  bbao replied 8 months ago.

You are welcome.

Customer: replied 8 months ago.
Did you forget about me?
Expert:  bbao replied 8 months 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.

Expert:  bbao replied 8 months 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?

Customer: replied 8 months ago.
I just want someone to help solve my issue. I have already paid for your expert advise. I would like to receive it.
Expert:  bbao replied 8 months ago.

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

Expert:  bbao replied 8 months ago.

Are you seeing the same thing on your Excel for Mac?

Customer: replied 8 months ago.
I cannot see the bottom of the page
Customer: replied 8 months ago.
the bottom of my page says circular A40
Expert:  bbao replied 8 months ago.

I can't see that.

Customer: replied 8 months ago.
no it does not appear on your page. Wonder why not?
Expert:  bbao replied 8 months 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.

Customer: replied 8 months ago.
Microsoft Excel for Mac 2011Version 14.5.0
Expert:  bbao replied 8 months 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.

Customer: replied 8 months ago.
thank you so much.
Expert:  bbao replied 8 months ago.

Check your settings and compare with my screenshot. Make sure Formula tab is in use.

Expert:  bbao replied 8 months ago.

The issue is in Column B, not A.

Expert:  bbao replied 8 months 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?

Customer: replied 8 months 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: replied 8 months ago.
By the way, the information that is pulled in is correct. It is just the circular problem that I have.
Expert:  bbao replied 8 months 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.

Customer: replied 8 months ago.
Ok I'll try that
Expert:  bbao replied 8 months ago.

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

Expert:  bbao replied 8 months ago.

Any update please?

Expert:  bbao replied 8 months 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?

Related Microsoft Office Questions