ActiveCell.FormulaR1C1 sheets names variables

[piopl]

Hey guys,

I have created this huge R1C1 formula and now I need to create a variable for the sheets names (NO1) and for the love of me I just simply can't. It works for the first INDIRECT, but for e.g. MATCH or FIND, it doesn't. Any ideas?

ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(""" & basicsh & "!"" & MID(ADDRESS(13,MATCH(" & ThirdSheet & "!R1C7,NO1!R13C1:R13C20,0)),FIND(""$"",ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C20,0)),1)+1,1) & ROW(INDIRECT(MID(ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C20,0)),FIND(""$"",ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C20,0)),1)+1,1) & MATCH(Copy!R[-1]C24,NO1!C[-14],0),TRUE)),TRUE):INDIRECT(""NO1!"" & MID(ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C20,0)),FIND(""$"",ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C200,0)),1)+1,1) & ROW(INDIRECT(MID(ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:R13C20,0)),FIND(""$"",ADDRESS(13,MATCH(NO1!R1C7,NO1!R13C1:C20,0)),1)+1,1) & MATCH(Copy!R[-1]C24,NO1!C[-14],0),TRUE))+COUNTIF(NO1!C[-14],Copy!R[-1]C24)-1,TRUE))"

Thanks and regards,
piopl

 

[Andrew Poulsom]

Welcome to MrExcel.

My immediate response is why do you need such a complex formula? What's your data layout and what result do you want to return?

 

[piopl]

Thanks for having me!

We have a sheet from which the data is being pulled with a drop-down list per months + with a different set of ranges i.e. 1st value in a table has 2 rows of data, 2nd has 4 and so on. This result should be the sum of that ranges based on a value from that drop-down list. It has to be a formula, because that value may vary for different countries.

That being said, I have already fixed that. I tried to do that for like a day and the thing that is missing is (as usual) " ' ", so it should be:


ActiveCell.FormulaR1C1 = "=SUM(INDIRECT(""" & basicsh & "!"" & MID(ADDRESS(13,MATCH('" & CStr(basicsh) & "'!R1C7,'" & CStr(basicsh) & "'!R13C1:R13C20,0)"... applied to all variables.

Thank you, though, for a quick response!

 

[piopl]

Hi again,

Unfortunately, it seems that it does not work. There is no problem with a formula now as it was before, but now I receive the #REF! error if the sheets name is a bit long (like 20 char). Does the INDIRECT formula have any length limitations for a ref_text value?

 

[Andrew Poulsom]

I don't think so. Can you post a sample of your data? It may be possible to shorten your formula considerably.

 

[piopl]

It's a confidential data, so I can't, sorry.

I have already resolved it and again - missing " ' " in the first part of the formula. INDIRECT could not see it as text, so the obvious REF error.