I need to update the following formula

I am trying to update the following formula to be able to return a both numbers and text.

=IFERROR(INDEX('R:\Reports\Reports\[Project Report - MF.xlsx]Projects'!$R$22:$R$100,MATCH($G22,'R:\Reports\Reports\[Project Report - MF.xlsx]Projects'!$D$22:$D$100,0))&"",IFERROR(INDEX('R:\Reports\Reports\[Pipeline Report 8-28-13.xlsx]MF Projects'!$N$22:$N$100,MATCH($G22,'R:\Reports\PIPELINE Reports\[Pipeline Report 8-28-13.xlsx]MF Pipeline Projects'!$G$22:$G$100,0))&"",""))

The cells I am looking at in the formula will have 1-8", 1234', and other punctuations...
LVL 1
wrt1meaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wrt1meaAuthor Commented:
And it appears, could be wrong, that if it doenst find data in the first location, its not checking the second locatoin...just returning a blank....
0
redmondbCommented:
Hi, wrt1mea.

Please see attached. Your formula appears to be working fine for me - it's certainly doing the second lookup.

Please post a redacted version of your files.

Thanks,
Brian.Pipeline.xlsx
0
wrt1meaAuthor Commented:
I am trying to figure out a way to post a redacted file...

What I am running into is that its looking at one place, either the first or second cell value, and it has data there, it returns it. If not, it returns blank. Not sure if it matters, but I am trying to return numbers OR numbers with Text.

I will be trying to get a redacted version out but it wont be til Tuesday.

I really appreciate the help...its getting the better of me for sure.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

redmondbCommented:
wrt1mea,

No worries, we're here to help!

You can either give me redacted versions of the three files or just update the details in the file I posted above.

Regards,
Brian.
0
wrt1meaAuthor Commented:
Please see the attached...Its the best I can come up with. On the production sheet, it seems to return data only from one sheet, whether it has a blank or not. And its not consistent on which one...

i just need it to return data, whatever type of data it may be, if one cell has data and the other doesnt...

Does it matter which order I put the formula in???

I really appreciate all of your help.
Report-8-30-13.xlsx
0
redmondbCommented:
wrt1mea,

Thanks for the file, but I'm afraid that things are still very unclear...
(1) Where is the formula? Please add it in to the file.
(2) Which sheet is the "production sheet"?
(3) What data is being returned and from where?
(4) "if one cell has data and the other doesn't"...
 - Which cells?
 - What if they both have data?
 - What if neither has data?

Thanks,
Brian.
0
wrt1meaAuthor Commented:
1. Crap...I inadvertently cleared it....I need the formula to be in Column L, N, and O on "Pipeline Reports" sheet. I have been using Column G as my unique value.

2. The Production Sheet is really the "Pipeline Reports" tab.....I was referencing my Production Sheet as my live sheet with real data

3. The data I am looking for is in either the "Project Report" sheet or the "Pipeline Report 8-28-13" sheet.

4. I highlighted the cells that will have data and put comments there too.
        If neither have data, return blank.
        If both have data, I need it to return the info from the Pipeline Report 8-28-13 Sheet.
0
wrt1meaAuthor Commented:
Sorry for the confusion, I assumed you could read minds! Thanks again for your persistence.
0
redmondbCommented:
No worries, wrt1mea! We've all been so focused on something that we simply don't register that it isn't obvious to everyone else.

It may be an hour or two before I can work on this - I'll get on to it as soon as I can.

Regards,
Brian.
0
redmondbCommented:
wrt1mea,

Please see attached. A few points...
(1) There is a different formula for each column.
(2) I don't like the way it picks data from the two sheets. For example, suppose for an entry that one of the three "Pipeline Report 8-28-13" columns is blank, but the other two are not. Suppose further that the entry in "Project Report" has data in all three columns. The result will be that "Pipeline Reports" for that entry eill be a mix of the two other sheets' data.
The ideal situation would be that all three columns would use the same flag to decide which sheet from which to take the data. Is this possible?
(3) While two of the sheets have 79 ID's, "Project Report" only has 74.

Regards,
Brian.Report-8-30-13-V2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wrt1meaAuthor Commented:
1. Thanks!

2. Its really only supposed to look at two sheets and return a value if one is blank for each corresponding column. If both sheets have data, return the data from the pipeline report 8-28-13 sheet. I am trying to use the ID column as the flag column to determine what data to reurn.

3. Dont worry about this...there is a mismatch in the example I provided. I will make sure my production sheet is identical.


I tried the first formula on my production sheet. Its only returning a value from the Project Report tab and not the Pipeline Report 8-28-13 tab. It should look at the Project report tab, if it has data, return that value only if the pipeline report 8-28-13 tab cell value is blank. If  pipeline report 8-28-13  has a value, return that value.


Ready to give up yet??? haha, I really appreciate your tenacity. I am back in the office so my turn around should be quicker.
0
redmondbCommented:
wrt1mea,

Have you tested the file from my last post?

Thanks,
Brian.
0
wrt1meaAuthor Commented:
I tested the formula you provided on my production sheet....

I tried the first formula on my production sheet. Its only returning a value from the Project Report tab and not the Pipeline Report 8-28-13 tab. It should look at the Project report tab, if it has data, return that value only if the pipeline report 8-28-13 tab cell value is blank. If  pipeline report 8-28-13  has a value, return that value.
0
redmondbCommented:
wrt1mea,

As I can't have your live files, I need you to test the file we do share!

Once that's doing what you want, we can then see what needs to be done to make it work with your live files.

Regards,
Brian.
0
wrt1meaAuthor Commented:
Ok...i will try first thing in the morning...i have been all oer the place with work so my apologies...
0
redmondbCommented:
No problem. Talk to you then.
0
wrt1meaAuthor Commented:
OK, I tested out the sheet you provided, and of course everything works perfectly. When i try on my production sheet, it doesnt work.

I am reworking the formula on the production sheet. I will keep you posted.
0
wrt1meaAuthor Commented:
OK...you are prolly gonna kill me....

I reworked the formula in my prod sheet and noticed I didnt change the range G:L, I left it as G:G....so, yeah, that's my bad.

Thanks for all of the communication and following through to the end on this!

Thank you very much...

I am sure I will be posting something in the near future.
0
redmondbCommented:
Thanks, wrt1mea.

No issue about the range - it's just too easy for something like that to happen!

Regards,
Brian.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.