Why can't I use CONCATENATE here?

[Mhowells]

Hey all,

I have a line of formula, and I am trying to replace a small part of it with another piece of code that will arrive at the same answer in a different way. The original line of code works fine. The snippet of additional code works fine. However, when I insert the snippet, Excel tells me that I have made an error in the formula and will not calculate it.

I can't really explain what the code does as I don't fully understand it. It is a part of a three column array to create a list of unique headings for a pull down menu. The part I want to replace has been marked with {French brackets}.

=(IF(LEN((OFFSET(Data!$G$1,1,0))),SUMPRODUCT(((OFFSET(Data!$G$1,1,0))>OFFSET({Data!$G$2},0,0,$AA$1,1))+0)+1,""))

What I would like to replace it with:

(CONCATENATE("Data!$G$",AD3))

AD is a column of numbers from 1 to 1000 - The logic is that the formula acts on one line lower in each line of the formula. Because of how data is added and removed from the spreadsheet by the user, the automatic system for doing this keeps failing, so I am trying to circumnavigate this via code...

Anyway, I realise that this is quite vague... Does anyone have any suggestions as to why I can't replace the first portion of code with the second? Even better, does anyone have any suggestions on how to fix it?

Cheers,

Max

 

[NBVC]

I'm guessing... but I think you want the Indirect() function.

Try:

=IF(LEN((OFFSET(Data!$G$1,1,0))),SUMPRODUCT(((OFFSET(Data!$G$1,1,0))>OFFSET(INDIRECT("Data!$G$"&AD3),0,0,$AA$1,1))+0)+1,"")

 

[Mhowells]

Nope, it doesn't like that either. Is OFFSET picky about the way it accepts cell Refs?

 

[just_jon]

Nope, it doesn't like that either. Is OFFSET picky about the way it accepts cell Refs?

OFFSET expects a reference to a Range Object.

Concatenation returns a String.

You would need, as mentioned perhaps via INDIRECT, to change the string into a reference.

 

[barry houdini]

I can't see why NBVC's suggestion won't work, what result do you get using that?

I take it AD3 is a number? What's in AA1?

An alternative to INDIRECT would be INDEX, i.e.

=IF(LEN(OFFSET(data!$G$1,1,0)),SUMPRODUCT((OFFSET(data!$G$1,1,0)>OFFSET(INDEX(data!$G:$G,AD3),0,0,$AA$1,1))+0)+1,"")

although you could probably use COUNTIF instead of SUMPRODUCT, i.e.

=IF(LEN((OFFSET(data!$G$1,1,0))),COUNTIF(OFFSET(INDEX(data!$G:$G,AD3),0,0,$AA$1,1),"<"&OFFSET(data!$G$1,1,0))+1,"")

and couldn't you also dispense with some of the OFFSETs?

 

[Mhowells]

I eventually did get it to work, but I don't know why it didn't work before. For your edification, the full line of code is as follows.

=(IF((AD3<$AE$1),(IF(LEN((OFFSET(Data!$G$1,1,0))),SUMPRODUCT(((INDIRECT("Data!$G$"&AD4))>OFFSET((INDIRECT("Data!$G$2")),0,0,$AA$1,1))+0)+1,"")),"Null Value"))

In answer to your question, column AA contains another formula. Overall, there are three columns of formulae which together produce a list of all entries in another list, but only one incidence of each and in alphabetical order. All three were taken from a post by Aladin Akyurek, and I genuinely don't know how they work. Saying this, I have been adapting these to work for this situation. I use OFFSET a lot because I am trying to avoid directly referring to cells that are likely to get deleted by the users of the spreadsheet. I found that if Row 2 (1 being an index line) were to be deleted, it through everything else off. By indirectly referring to them with OFFSET, I was able to avoid this problem.

I am really new to all of this and am teaching myself as I go... Before 9 weeks ago, I had only written the most basic arithmetic based formulae, and have sort of moved on from there...

Cheers,
Max