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., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 6960

Experience: Computer Software Specialist for more than 10 years

49766785

Type Your Microsoft Office Question Here...

Jess M. is online now

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.

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.

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

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

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 2 years 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 2 years 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.

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

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)

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

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?

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.

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 2 years 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.

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?

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 2 years ago.

Or is there no returned value? if it is all blank below F2 then it isn't working at all.

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?

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.

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 2 years 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 "".

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?

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 2 years 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?

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.

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?

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 2 years ago.

either way, can i just copy and paste this information into another workbook and avoid the corruption?

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.

Thats the last thing i sent you. http://ge.tt/8woYakK2/v/0

Customer:replied 2 years 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.

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.

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 2 years 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

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

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.

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

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

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 2 years 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 2 years 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.

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.

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?

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.

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.

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

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

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 2 years 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 2 years ago.

ugh nevermind it isn't working on SCH 40 BLK page

Customer:replied 2 years 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 2 years ago.

okay that works for whole numbers. not other ones.

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.

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.

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.

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