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: 189
Experience:  Certified Information Systems Security Professional / Certified Information Security Manager / Microsoft Most Valuable Professional
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: 25 days ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 25 days 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 25 days 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 25 days ago.

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

Customer: replied 25 days ago.
test.xls
Customer: replied 25 days ago.
The circular reference comes from the names in column A.It begins with A40 and moves upwards.
Customer: replied 25 days 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 25 days ago.

i need playerstats.xls to test

Expert:  The-PC-Guy replied 25 days ago.

there is a link to that file

Customer: replied 24 days ago.
Ok....Here it is
Expert:  The-PC-Guy replied 24 days 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 24 days ago.
let me look....
Customer: replied 24 days ago.
but they are different rows?
Customer: replied 24 days 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 24 days ago.

ok, your right

Expert:  The-PC-Guy replied 24 days ago.

let me check something else

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

this is what I am getting.

Expert:  The-PC-Guy replied 24 days ago.

am i supposed to be getting something different.

Customer: replied 24 days 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 24 days 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 24 days 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 24 days ago.

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

Customer: replied 24 days 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 24 days ago.

teamviewer i believe there is a mac version

Customer: replied 24 days 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 24 days ago.

sorry the site always charges extra for remote support.

Expert:  The-PC-Guy replied 24 days 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 24 days ago.

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

Customer: replied 24 days 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 24 days 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 24 days ago.
Thanks. I appreciate your efforts.
Customer: replied 24 days ago.
Nobody has tried to contact me yet. Can you please find out when I should expect a contact?thank you
Expert:  bbao replied 24 days ago.

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

Customer: replied 24 days 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 24 days 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 24 days ago.
thanks...will be awaiting your reply
Expert:  bbao replied 24 days ago.

You are welcome.

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

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

Expert:  bbao replied 24 days ago.

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

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

I can't see that.

Customer: replied 24 days ago.
no it does not appear on your page. Wonder why not?
Expert:  bbao replied 24 days 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 24 days ago.
Microsoft Excel for Mac 2011Version 14.5.0
Expert:  bbao replied 24 days 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 24 days ago.
thank you so much.
Expert:  bbao replied 24 days ago.

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

Expert:  bbao replied 24 days ago.

The issue is in Column B, not A.

Expert:  bbao replied 24 days 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 24 days 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 24 days ago.
By the way, the information that is pulled in is correct. It is just the circular problem that I have.
Expert:  bbao replied 24 days 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 24 days ago.
Ok I'll try that
Expert:  bbao replied 24 days 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 23 days ago.

Any update please?

Expert:  bbao replied 15 days 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?