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 Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6131
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

How do i find the secret formatting on these cells? I have

Customer Question

How do i find the secret formatting on these cells?
I have a table with multiple match values, and there seems to be some sort of arbitrary formatting that I am unaware of.
I have a formula (that works fine on another worksheet) that will ultimately return multiple search values that meet a criteria. Now, I am searching for the number 2 with this formula, and it returns no values. I can clearly see there is a 2 in the first column of search. This 2 is not recognized.
This 2 is text, middle allignment, allign text left, Calibri, font size 11.
I delete this 2 and type in 2 WITH THE EXACT SAME PROPERTIES, and my formula comes up with an answer. (neither of these 2's are numbers stored as text)
It is frustrating to not understand what these 'hidden' formats are, and retyping the information isn't really an option because there are near a thousand entries.
So how do i re-format a column to be EXACTLY how it was but somehow gain the seemingly intangible qualities of actually typing in the value?
--Note: i could give my formula for searching for these values, but that doesn't seem to be the heart of my problem, the core of this problem is the fact that i don't understand the difference between a typed in 2 and a 2 with the same properties that was already in the table.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Richard replied 1 year ago.
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
Can I look at your file please to see this?
You can provide it to me by uploading it at http://ge.tt
And provide me the download link please.
Customer: replied 1 year ago.
http://ge.tt/4Y8H3kK2/v/0?c
Customer: replied 1 year ago.
Copper ACR Brazed is the section that mostly works, the SCH category is the one that doesnt work at all.
Expert:  Richard replied 1 year ago.
so you mean SCH 40 BLK 150# ***** ?
And is it column G that has the problem?
Customer: replied 1 year ago.
Does it cost extra to make a phone call?
Customer: replied 1 year ago.
You can type 2 into cell B10 and get a result. The problem column is technically F, but i think the problem lies in the data entry.
Expert:  Richard replied 1 year ago.
Nothing changes when I enter 2 in B10
F is blank and G looks at the Piping sheet
can you reply on my question please
so you mean SCH 40 BLK 150# ***** ?
A phone call does cost more
Customer: replied 1 year ago.
Yes I do mean SCH 40 BLK 150# ***** Do you see the same value works in Copper ACR Brazed as well though?
Expert:  Richard replied 1 year ago.
No sorry, if I put 2 in B10 I see nothing at all change.
What should actually be happening? Can you explain what it should be doing.
So enter 2 in B 10
Then cell ? should look up ? ect
Customer: replied 1 year ago.
So basically these are creating a dependent search bar (found in piping 1) that can be looked through. So, when i type in a value into that referred to Piping 1 C6 value, this should be the filtered list of all things matching say "2". So as you can see in Copper ACR Brazed, the 2 gives an alternate list that (mostly) shows all item names with 2 in the same column. So, the same thing should be happening in SCH 40 BLK 150# ***** If I type in 2, there should be many values in the F column, first being A53 BWS40 BK PE PIPE, the next being GAS COCKS, etc etc.
Customer: replied 1 year ago.
So what happens in mine is in the SCH 40 BLK 150# ***** page i have no results with the 2.
THEN i retype a 2 into b10 (as far as i can tell the exact same 2 value) and I get a result.
That shouldn't happen from my understanding because i think they are the exact same 2.
Customer: replied 1 year ago.
I am not sure why you aren't getting that result, but at least you can compare the Copper ACR Brazed and the SCH 40 BLK 150# ***** see the completely different results.
Expert:  Richard replied 1 year ago.
ok, so basically F on SCH 40 BLK 150# ***** should display the first product it finds in piping1 that has been listed?
Even though B on piping only has 1 value to actually select? As piping only has one value and it has no 2 in it
Customer: replied 1 year ago.
to first line- Yes F9 should have the first instance that the match in F2 (linked to C6 piping 1) is found.Sorry as piping only has one value in it? the value that is typed into piping c6 is searched for all entries in the Copper ACR BRazed and SCH 40 BLK 150# ***** in SCH 40, there are many values of 2 (the first two are seen on the page of the A53 BWS40 BK, and GAS COCKS)
Expert:  Richard replied 1 year ago.
Sorry I just do not understand what your sheet is doing.
Can you tell me please, very detailed exactly what I should be looking at.
So enter 2 in B 10
Then cell ? should look up ? ect
Customer: replied 1 year ago.
Ok, the purpose is to look through these two pages: Copper ACR Brazed and SCH 40 BLK 150# ***** and help for pricing projects. Piping 1 has these comboboxs that you can type information into. These boxes refer to all of the availible items you can type into. Theoretically, when you have filled in 2 boxes, the third box should no longer have all options availible to be typed in, but INSTEAD should have only the values that are still theoretically possible. So if i type in Copper ACR Brazed into category and 90 Elbow - LR into Item i get a significantly shorter list which is how it should be. Those are the only availible values meeting those 2 criteria. This works well for the Copper ACR Brazed page.Now when i try to do the same thing referring to the SCH 40 BLK 150# ***** page, i face problems. As you can see on both pages: Copper ACR Brazed and SCH 40 BLK 150# ***** this dependent list is to be generated on the side. In copper ACR Brazed, on the right side of the table (if a value is typed in to size on piping 1), you can see a generated list of the dependent search.
So my question is why is this same list not being generated in SCH 40 BLK 150# *****? it is the exact same formula dealing with a table. Why is 2 returning a value in one table and not another when 2 is a value found on both tables?
Expert:  Richard replied 1 year ago.
Sorry, I just do not understand what you mean.
Can you just tell me please as Im requesting
Type this in sheet this, Cell that and you will see this result in cell
This is how I need it explained please as its a big sheet and you know it well, as you worked on it, but I need to understand at that level what your trying to do.
Customer: replied 1 year ago.
Ok. go to Piping 1 and hit reset (on A6 and make sure you aren't on design mode) this shoudl clear the combo box values.
Now go to Size and type in 2.
Now go to SCh 40 BLK 150# *****
There should be a value of 2 in F2 and below in the F column there should just be a bunch of blank values.
Now, when i go to B10 and delete the 2 found in that cell and instead type in 2, the cell F9 will find a value.
Customer: replied 1 year ago.
Oh, also go to file-options-formulas- and enable iterative calculation @ 100 Maximum Iterations and .001 change. This should stop those pesky circular reference warnings.
Expert:  Richard replied 1 year ago.
ok, that helps a lot, thank you
Except... it works perfectly for me, no need to type it in B10 at all
But, when you type 2 in on piping 1, then click into another cell, outside of C6
Does it then work for you?
Customer: replied 1 year ago.
i did click off of the cell.
What do you mean it works perfectly for you? So with the condition set forward (from my previous post), when you go to SCH 40 BLK 150# ***** there is a list of values from F9 down?
Customer: replied 1 year ago.
Or is there no returned value? if it is all blank below F2 then it isn't working at all.
Expert:  Richard replied 1 year ago.
yes correct, F9 is populated.
So I enter in C6 2, and F9 is populated.
And not just with, but on any value i choose from the dropdown
Customer: replied 1 year ago.
You are entering into the combobox and not directly into c6 correct?
Expert:  Richard replied 1 year ago.
either work no problem
Either enter it in or select
Customer: replied 1 year ago.
it does not work for me. I am very confused as to why it would be different.I just uploaded what my current result looks like.
Expert:  Richard replied 1 year ago.
you need to give me the download link please
Customer: replied 1 year ago.
http://ge.tt/8YVcJkK2/v/0
Expert:  Richard replied 1 year ago.
ok, this version is corrupted as it gives me a system resource error which always points to a corrupted file, this one wont work, but download the one from me that does work.
http://ge.tt/6PtYKkK2/v/0?c
Does it work for you?
Customer: replied 1 year ago.
I typed in 2 into the one you gave me and i went to SCH 40 BLK 150# ***** and have NO results.
http://ge.tt/7qG0LkK2/v/0
Expert:  Richard replied 1 year ago.
which version of excel are you using?
Customer: replied 1 year ago.
Excel 2007 Macr-Enabled Workbook
Expert:  Richard replied 1 year ago.
ok, i have been playing around and it and now it stopped working
so looks like an bug somewhere
Did this problem start for you all of a sudden?
Customer: replied 1 year ago.
I dont really think so. I think I eventually thought up the formula for the Copper ACR Brazed page, and that worked immediately. And assumed that the same formula would HAVE to work for the next page and it never did.
Expert:  Richard replied 1 year ago.
no, the formula would not work for another page, you would need to update it
Whats the formula you used?
Customer: replied 1 year ago.
working formula on Copper ACR Brazed- {=IF(ISERROR(INDEX($B$4:$C$433,SMALL(IF($B$4:$B$433=$F$2,ROW($B$4:$B$433)),ROW(1:1)),2)),"",INDEX($B$4:$C$433,SMALL(IF($B$4:$B$433=$F$2,ROW($B$4:$B$433)),ROW(1:1)),2))}Non-working formula on SCH 40 BLK 150# *****
{=IF(ISERROR(INDEX($B$4:$C$821,SMALL(IF($B$4:$B$821=$F$2,ROW($B$4:$B$821)),ROW(1:1)),2)),"",INDEX($B$4:$C$821,SMALL(IF($B$4:$B$821=$F$2,ROW($B$4:$B$821)),ROW(1:1)),2))}
Customer: replied 1 year ago.
this formula is intimidating- but basically all it does is find if there is a match, and if there is a match it returns the 2nd column value, and if not then it returns "".
Expert:  Richard replied 1 year ago.
ok, give me some time to check out please
Expert:  Richard replied 1 year ago.
If you double click in F2 after making the selection, does it then work for you?
Customer: replied 1 year ago.
doesn't double clicking in F2 just take you to typing on the equation inside of F2?
Expert:  Richard replied 1 year ago.
yes, then click out and for me it then works.Looks like this file though is completly corrupted for things to work like this, its not the formula, thats ok. Do you have a previous version of this file?
Customer: replied 1 year ago.
I have done quite a bit of analysis on this and I think the problem stems from how the numbers on the SCH 40 BLK page were input. Those 2 equations are exactly the same except for the ranges, yet on equation works on a spreadsheet and the other doesn't
Customer: replied 1 year ago.
A previous version? the latest i have still doesn't work. How can you un-corrupt a file, and are you SURE that is the problem?
Expert:  Richard replied 1 year ago.
you cant un corrupt a file, and yes its the problem as the formula works, just sporadically But I am not asking about the latest one but an earlier version.
Customer: replied 1 year ago.
http://ge.tt/7XdVXkK2/v/0
is the earliest.
Expert:  Richard replied 1 year ago.
ok, and you still have the problem with this file as well?
Customer: replied 1 year ago.
I have a discovery that hopefully pushes away some of the things that aren't causing the problem.
So i have a file that is just the referenced tables (Copper and SCH books) and I copied and pasted the F column onto it so it looks like this: http://ge.tt/7HOzXkK2/v/0And when I delete that 2 (which is a result of Piping 1 from that other workbook), and type in 2 instead I get results.
So doesn't that mean that these two 2's are just formatted differently in some way?
Customer: replied 1 year ago.
And yes i do.
Expert:  Richard replied 1 year ago.
no, right click and select format, you then see the format as being general for both. Its the first thing I checked
Customer: replied 1 year ago.
yeah i know. I just dont understand (aside from your corruption explanation) how a value being 2 (general) and me deleting that and typing in 2 (general) can return completely different results.
Customer: replied 1 year ago.
either way, can i just copy and paste this information into another workbook and avoid the corruption?
Expert:  Richard replied 1 year ago.
its excel, it can get corrupted, its really best to make a new sheet Copy and paste, testing on each time and it should then work again.
Customer: replied 1 year ago.
OK i really appreciate your help, but i feel like there must be some extra format you can't see.
So on my original furthest progress book, I changed the F2 cell references in the two workbooks: Copper ACR Brazed and SCh 40 blk to c7 instead of c6. Then i typed in 2 and it WORKED for the SCH 40 BLK and didnt work for the Copper ACR Brazed.
Customer: replied 1 year ago.
typed in 2 to c7*
Expert:  Richard replied 1 year ago.
yes and that works, but there is no extra "format" you cannot see. Excel does not have that
Customer: replied 1 year ago.
http://ge.tt/8woYakK2/v/0
isn't working.
Expert:  Richard replied 1 year ago.
yes, as I have been saying the workbook is corrupted..
Customer: replied 1 year ago.
i put those into another workbook. That was a new one then i copy and pasted values.
Expert:  Richard replied 1 year ago.
you have to go one by one till you see it stop working and see on which point of what you copied over it stopped working
Customer: replied 1 year ago.
Sorry, copy over each page one by one? or copy each column one by one? I copied each page over 1 by 1.
Expert:  Richard replied 1 year ago.
Page, just do one page, make sure its working, then the next
Customer: replied 1 year ago.
I did the first page, it worked. I did the second page, it didn't.
Expert:  Richard replied 1 year ago.
let me see what you now have please
Customer: replied 1 year ago.
Thats the last thing i sent you.
http://ge.tt/8woYakK2/v/0
Customer: replied 1 year ago.
You can make F2 on both pages = 2, then the SCH 40 BLK page works, or you can make it refer to the 2 found on that other book (the one you type into the combobox) and the Copper ACR Brazed page will work.
Expert:  Richard replied 1 year ago.
anything I put in F2 it works on
Customer: replied 1 year ago.
What the hell........
This is so frustrating I dont understand, is my entire excel file corrupt or something?
I have Excel 2010 on my home computer and i tried this problem on that and it didn't work.
Expert:  Richard replied 1 year ago.
If you open the file we just did on this other computer, do you have the same problem?
Customer: replied 1 year ago.
Yes. I have the exact same problem on a different computer.
Expert:  Richard replied 1 year ago.
sp on SCH 40 BLK you put a value on F2 and nothing changes?
Customer: replied 1 year ago.
I can type in 2 (instead of the formula refering to piping 1 c6) and it will work.
Expert:  Richard replied 1 year ago.
in the latest version there is no piping 1
Customer: replied 1 year ago.
Do you know what isn't working on this equation?
{=IF(ISERROR(INDEX($B$4:$C$821,SMALL(IF($B$4:$B$821=$F$2,ROW($B$4:$B$821)),ROW(1:1)),2)),"",INDEX($B$4:$C$821,SMALL(IF($B$4:$B$821=$F$2,ROW($B$4:$B$821)),ROW(1:1)),2))}
Even on the page it works on it returns a double of Sleeve which should be a Globe VLV.
http://ge.tt/5MHegkK2/v/0
is what the dependable list should be on Copper ACR Brazed with size 2.
Customer: replied 1 year ago.
The marked out stuff with the blue writing beside it is what should be-
Sleeve should return Globe VLV
Reduced 90 Elbow should return L-Soft
Expert:  Richard replied 1 year ago.
Sorry I have decided to opt out of this question as I am out of ideas.you do not need to do anything, another expert will notify you when they take over the case.Thank youRichard
Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.I am very sorry about your issues. I can also see that the arrow formula that you are using requires a NUMERIC data in cell F2 as the lookup cell. That is also the reason why the array formula works when you manually type 2 in the F2 cell.
Expert:  Jess M. replied 1 year ago.
Now, since the array formula is expecting a NUMBER in F2 and your current layout does not output 2 as a number using your formula LOOKUP(2,1/('Piping 1'!C6:C20<>""),'Piping 1'!C6:C20), all you need to do is to convert it to a number using the VALUE formula. This will work since 2 is a number but just in TEXT or General format.Please use this formula in your F2:=VALUE(LOOKUP(2,1/('Piping 1'!C6:C20<>""),'Piping 1'!C6:C20))It shall work, I have tested it in the most recent file you uploaded, Book153.xlsm.Please give that a try and let me know your results so that I can help you further.I hope that helped.Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.Thank you!Best regards,Jess
Customer: replied 1 year ago.
Okay well that wasn't the actual most recent file I'm working on----
Richard thought he would need my oldest file.
My actual newest file is:and I only need to refer to Piping 1 C6, not the lowest non-blank value.
I tried the =VALUE('Piping 1'!C6) in F2 and it didn't work. it returned no results
Customer: replied 1 year ago.
this is the file* http://ge.tt/6XQ1tnK2/v/0
Expert:  Jess M. replied 1 year ago.
Please give me a moment to download this actual file.
Expert:  Jess M. replied 1 year ago.
Wait, are you also working on the sheet "SCH 40 BLK 150# *****"?
Customer: replied 1 year ago.
Here is the actual file:
http://ge.tt/84zovnK2/v/0
This is what the value of Copper ACR Brazed with a 2 typed in SHOULD be:
http://ge.tt/23rCwnK2/v/0
Customer: replied 1 year ago.
I crossed out the incorrect values and put the correct values in the blue ink on the right side of the writing
Customer: replied 1 year ago.
Yes i am but the problem lies on both of the Copper ACR Brazed and SCH 40 BLK 150# ***** pages. The search SHOULD return all search value meeting the criteria.
Expert:  Jess M. replied 1 year ago.
Ok please give me a moment
Customer: replied 1 year ago.
On SCH 40 BLK 150# ***** value works if the number is ***** integer that was typed in. BUT if the number was a fraction like 1/2 then it returns the absolute value of that number and gets like 42006 or something.
Expert:  Jess M. replied 1 year ago.
I noticed that too. It is because nature of text and numeric data involved in your worksheets. Can we use helper columns for this?
Customer: replied 1 year ago.
Sure. I can use them to just replace the data in column B.
Expert:  Jess M. replied 1 year ago.
Ok, this can take some time. Please give me a moment to build the helper columns for you so that your original array formulas will work.Also, do you really need to refer your F2 to Piping 1 C6? Can we use a data validation in F2 instead containing the values in B?
Customer: replied 1 year ago.
Well it would need to filter the values in B and give all results that match up with the value typed in to Piping 1 C6.
Expert:  Jess M. replied 1 year ago.
So the value in F2 should depend on Piping C6?
Customer: replied 1 year ago.
Yeah. I mean we could change the entire formula to generate a list- but the list needs to be filtered based on the value of C6.
Expert:  Jess M. replied 1 year ago.
I understand, so we really need to reference F2 to Piping C6. Please give me a moment to build the sheet for you.
Customer: replied 1 year ago.
Have you found anything out?
Expert:  Jess M. replied 1 year ago.
I have not completed the rebuilding of your sheet using helper columns. I am still working on it now.
Customer: replied 1 year ago.
I didnt realize it was this labor intensive. How is it coming along?
Expert:  Jess M. replied 1 year ago.
I am still working on it. It is tricky since the format in F2 (general/numeric) must match with the data format in column B. Any mismatch of format will be missed or bypassed by the search.
Customer: replied 1 year ago.
It's worth noting that in the copy i gave you, the page SCH 40 BLK 150# ***** F2 is referring to Piping 1 C4, which is part of a work-around method that i was trying to use. If the data formatting changes how that works then I will just turn that to piping 1 c6.
Expert:  Jess M. replied 1 year ago.
Hi, thank you for your patience. I have tried several procedures but not one fixes all your requirements. But I am still working hard on this one and this is indeed tricky and challenging. The most difficult part of the nature of the data in your SIZE column since they are a combination of text and numbers. Fractions there are texts while whole numbers are numeric in General format.They can be a good combination but the problem is that, you are using the SMALL function in your formula and that is expecting numbers as parameters.Please give me more time for this and thank you very much.Best regards,Jess
Customer: replied 1 year ago.
Hey have you made any progress on that?
Expert:  Jess M. replied 1 year ago.
Yes, this is tricky but I am having progress. One thing, can I eliminate the minus signs in your mixed number fractions? Like instead of 5 - 1/2, just replace - with a space so it will be 5 1/2
Customer: replied 1 year ago.
yeah that would be fine.
Expert:  Jess M. replied 1 year ago.
I was successful in the SCH 40 sheet but in the Copper ACR the problematic results are still there
Customer: replied 1 year ago.
oh wow! thank you, ***** ***** have any idea why the problematic results are happening?
Customer: replied 1 year ago.
it seems to me like the data could somehow be converted to the 'formatting' that the SCH 40 page has.
Expert:  Jess M. replied 1 year ago.
I already did that and the cell format were messed up so I needed to retype the data even after setting the format to fraction.
Customer: replied 1 year ago.
Hey ijust tried a new formula that worked for the situation I gave you.
{=INDEX($C$5:$C$433, SMALL(IF(($F$2=$B$5:$B$433)*(COUNTIF($F$7:F8, $C$5:$C$433)=0),ROW($B$5:$B$433)-MIN(ROW($B$5:$B$433))+1,""), 1))}
There is an error in it, but it gave me the correct results for the one that I looked up. I am gonna try it with other ones.
Customer: replied 1 year ago.
Yeah this works
{=INDEX($C$5:$C$433, SMALL(IF(($F$2=$B$5:$B$433)*(COUNTIF($F$7:F8, $C$5:$C$433)=0),ROW($B$5:$B$433)-MIN(ROW($B$5:$B$433))+1,""), 1))}
I just dragged it down from F9 in Copper ACR Brazed
Customer: replied 1 year ago.
ugh nevermind it isn't working on SCH 40 BLK page
Customer: replied 1 year ago.
Try this with the new, fixed SCH 40 BLK 150# *****
{=INDEX($C$5:$C$433, SMALL(IF(($F$2=$B$5:$B$433)*(COUNTIF($F$9:F9, $C$5:$C$433)=0),ROW($B$5:$B$433)-MIN(ROW($B$5:$B$433))+1,""), 1))}
I need to make it not present #NUM! errors, but this seems to give the correct results.
Customer: replied 1 year ago.
okay that works for whole numbers. not other ones.
Expert:  Jess M. replied 1 year ago.
Oh, you have another set of formulas... hmmm I will try them on my revisions. What I did in the original file that you gave me was to convert the data into fraction format but they need to be re-entered manually that took me some time. I will keep you posted.
Customer: replied 1 year ago.
Oh that was just a suggestion. Re-entering formulas is ultimately the best way. The formula i had tended to not work on fractions again so we are where we started.
Expert:  Jess M. replied 1 year ago.
I understand. But I still believe that the data on the SIZE will still need to be re-entered so that fractions will be correct, without the - and shall be replaced with a space.
Customer: replied 1 year ago.
Yeah i mean i said earlier that was fine. How do you think this is coming along? I am trying to finish it this week
Expert:  Jess M. replied 1 year ago.
Hi, I have tried a lot of workaround in your worksheet but they will not work across all your requirements considering the data you have especially the SIZE column with a mix of numbers and text.Please allow me to pt out so that other exprts can join you. You will be notified through email when someone joins you here.Thank you,Jess
Customer: replied 1 year ago.
Hey any new technician on the way?
Customer: replied 1 year ago.
Hey it has been 3 days and I am really trying to finish this by the end of the week. Any progress on a new person?