Unable to copy Natural language formula down the column

[Geetha Gupta]

Hi All,

I have filled a natural language formula at the end of the row. So only column headings have been used in the formula. In fact there are no row headings, as they are not reqd anyway.

Some cells in the formula needed to be referenced in A1 style. Else the formula does not seem to be working. Also, on pressing F2 in the cell with the formula, I find that cells referenced with column headings do not have the fill handle but cell referenced in A1 style have the fill handle.

My main problem is that I find is that I am unable to copy the formula down the column. I am getting #NAME error on copying by any method – whether using ctrl+D or dragging with the fill handle or using copy and paste buttons in the edit menu.

Can someone explain why this is happening.

Thanks in advance

Regards,
Geetha

[shg]

Natural language formula?

[Glenn Kennedy]

I think that you need to attach an Excel sheet so that we can see what you're trying to do. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[Geetha Gupta]

Hi Glenn & shg,

i have attached a file with sample data.

See sheet(USES) in the file. The rows which have the formula working incorrectly have #NAME error instead of the correct result

Regards,
Geetha

[Geetha Gupta]

Hi Glenn & shg,

i have attached a file with sample data.

See sheet(USES) in the file. The rows which have the formula working incorrectly have #NAME error instead of the correct result

Regards,
Geetha

[Glenn Kennedy]

I cannot help you - I am using Excel 2010 (earliest available to me is 2007). Neither version now allows the use of labls in formulae. Excel tried to replace them with cell references and i, too get the #NAME error and the formulae (e.g. H12 ) look tlike this

= IF(ISERR(SEARCH("/",#NAME?)),G12,LEFT(#NAME?,(SEARCH("/",#NAME?)-1)))& IF(#NAME?=".", "","_" & #NAME? )& IF(#NAME?=".", "","_" & #NAME?) & IF(#NAME? =".", "", "_" & #NAME? )&IF(#NAME?=".", "", "_" & #NAME?) & IF(#NAME?=".","", "_" & IF(ISERR(SEARCH("/",#NAME?)),C12,LEFT(#NAME?,(SEARCH("/",#NAME?)-1))) )

What was in that cell in your version of Excel (you are using Excel 2000??)

[Geetha Gupta]

Hi Glenn,

Your question -
"What was in that cell in your version of Excel (you are using Excel 2000??)"

Yes I use Excel 2000. (For so many reasons i like this version. newer versions lack some features that i need.)

i don't understand your question. Which cell are u talking about?

Regards,
Geetha

[Glenn Kennedy]

As stated in my post, H12 (of USES).

[Geetha Gupta]

Hi Glenn
Thanks for this super quick response.

there was nothing in cell H12. Just this formula.

From H15 downwards, to H138, in the older rows, the formula is giving correct results.
the formula in those rows is, eg H18

= IF(ISERR(SEARCH("/",'Con1')),G18,LEFT('Con1',(SEARCH("/",'Con1')-1)))& IF(Con2=".", "","_" & Con2 )& IF(Con3=".", "","_" & Con3) & IF(PartAffected =".", "", "_" & PartAffected )&IF(Prop2=".", "", "_" & Prop2) & IF(Prop1=".","", "_" & IF(ISERR(SEARCH("/",'Prop1')),C18,LEFT('Prop1',(SEARCH("/",'Prop1')-1))) )

you can see the column headings instead of #NAME in this correct version of the formula.

The problem started when i wanted to increase data rows. i had inserted some rows and copied the formula in the new rows and am getting this kind of incorrect result.

regards,
Geetha

[Glenn Kennedy]

This is what I see in H18...

= IF(ISERR(SEARCH("/",G$9:G$65536)),G18,LEFT(G$9:G$65536,(SEARCH("/",G$9:G$65536)-1)))& IF(F$9:F$65536=".", "","_" & F$9:F$65536 )& IF(E$9:E$65536=".", "","_" & E$9:E$65536) & IF(D$9:D$65536 =".", "", "_" & D$9:D$65536 )&IF(B$9:B$65536=".", "", "_" & B$9:B$65536) & IF(C$9:C$65536=".","", "_" & IF(ISERR(SEARCH("/",C$9:C$65536)),C18,LEFT(C$9:C$65536,(SEARCH("/",C$9:C$65536)-1))) )

if you drag this formula back up, the #NAME errors disappear. However, do you really need to search dow to row 65,536? can't you change that to something more realistic?

[Geetha Gupta]

Hi Glenn,

What you see in H18 and what i see in H18 are different.

Actually what this formula needs to do, as can be seen in my version of the formula in H18, is to pick up the column headings in row8. In your version of the formula, row8, the heading row has been completely left out - all search starts from row9 in each column down to the last row in that column, which is really unnecessary.

Here is the link to https://support.microsoft.com/en-us/kb/279412 for more info on this.

BTW, how do you drag up?

Regards,
Geetha

[Geetha Gupta]

Hi Glenn,

I found out what went wrong at last !!!!

i went to Tools> Options > Calculations. there, i found 'Accept labels in formulas’ checkbox unchecked. i checked the checkbox and then copied the formula once more into the relevant cells and the problem got resolved.

but why 'Accept labels in formulas’ checkbox got unchecked on its own in the first place is what i don't understand.

Thanks once again Glenn for your constant support

Regards,
Geetha

[Glenn Kennedy]

Glad to have contributed! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of my posts, if you think that I helped....