Why can't I use CONCATENATE here?

Mhowells

New Member
Joined
Aug 24, 2006
Messages
29
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Aug 24, 2006
Messages
29
Nope, it doesn't like that either. Is OFFSET picky about the way it accepts cell Refs?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Aug 24, 2006
Messages
29
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
 

Forum statistics

Threads
1,137,293
Messages
5,680,651
Members
419,923
Latest member
Kalthus

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
Top