Why can't I use CONCATENATE here?


New Member
Aug 24, 2006
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}.


What I would like to replace it with:


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?



Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I'm guessing... but I think you want the Indirect() function.


Upvote 0
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.
Upvote 0
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.


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


and couldn't you also dispense with some of the OFFSETs?
Upvote 0
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...

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back