VBA Alternative: INDIRECT created on Mac does not function the same in Windows

Hi - thanks for any advice help you can help with.

I've created a table and lookup in order to get a range of numbers based on the table. The table provides all possible outcomes, but I have a dynamic source that will determine the range in the table and produce the final range.

In Mac 365 I used the following

=TRANSPOSE(M20:INDIRECT(CONCATENATE("M",ROW(M20)+B7-B6)))

and it works wonderfully. It

• dynamically presents the range based on the source value
• increases or decreases the number of columns based on that range

Rationale on some of the formula

• I can't use concatenate because the range can be well over 8000+ ch limit per cell
• I need to transpose because I need to copy and paste the full range into another application (and rows/breaks are not carried over when pasted)
• It works because I can shift-control-right to find the full range to copy, without having to scroll endlessly to a specific column to shift-click

The problem I am having is that, this tool is not for me to use, it's for a workforce who are on Windows. When they open the file in Windows, the number of columns is static based on the range on the saved file that I provided. Meaning changing the Source value, will update the range to the same column only.

eg

• if a lower range max, it will produce NA in the subsequent columns
• if a higher limit, it will cut off at the max column of the file opened.

It also says "cannot change part of array" when I try and edit, nor does it recognise the same formula. It presents it as

{=TRANSPOSE(M20:INDIRECT(CONCATENATE("M",ROW(M20)+B7-B6)))}

Any advice to either help me rebuild in Windows Excel 365 to have the same behaviour?

Thank you so much!

EDIT: Sheet instructions

Change the value in B2 between 1 and 1500; this corresponds to B19 and below, the Sequence number range.
B10 is where the formula in question is

• if you enter "1" in B2, you will get a single range in B10
• if you enter "2" in B2, you will get a corresponding range in B10, C10
• if you enter "10" in B2, you will get a corresponding range in B10:K10
• Row 10 will increase in column values per the corresponding Sequence max

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Thank you!

Please try to change

from ROW(M20) to Cell("Row",M20)

Regards.

@Menem - there was no rule violation, therefore no need to remove your solution. It's only when we quote rules and specifically say there should be no further responses that you need to do this.

Another my try,

=Textjoin("",TRUE,INDIRECT(CONCATENATE("M20:M",ROW(M20)+B7-B6)))

But from your data, it will #value error due to length over 32,767 chars.

Note: I think that you use an array formula (by press Ctrl-Shift-Enter after enter formula) then copy to the right
and it maybe array formula behavior to merged them together so it can edit each of them.( I might be wrong)

Sorry, can't help much.

Regards.

Thank you so much, Menem for your try. The sample data is only up to 1500, but actually the full data set goes to 9,999 so it would very likely #value out due to length.

I'm not using any ctr-shft-enter actions, as this is entirely dynamic. The only value that is being inputted by user is B2 (or C18:J18 for purposes of choosing the page retention)

I just can't figure out why this works in Mac, and not Windows.

Any other advice from others?

I'm not using any ctr-shft-enter actions, as this is entirely dynamic.

However, you said this:

It presents it as

{=TRANSPOSE(M20:INDIRECT(CONCATENATE("M",ROW(M20)+B7-B6)))}

This means that Excel for Windows is seeing it as an array-entered formula and presenting it as such.

On my Windows machine, having opened your file, I see exactly the same as you do on your Mac - no errors anywhere.

I am on the insider channel, which means my 365 is completely up-to-date, and the TRANSPOSE formula is coming through as a SPILL formula, without the {}.

This is how I would interpret it, yes. I can't diagnose error messages that I'm not seeing, but telling you that I'm not seeing them with my version should help you to narrow this down.

Hopefully someone with a less up-to-date version can confirm what your co-workers are seeing.

Your Mac build is irrelevant.

That's correct. Unless your version is current enough to include spill functionality, your formulas won't adjust the way you want them to.

the Transpose function might not achieve it in Windows

It will if the version of Excel is up-to-date (current) - I have demonstrated this. It's not the platform that is at issue.

OK - I'm on bete 2007.

Your current set up definitely won't work on 2013.

No, but I can move the thread to the VBA section - I’ll do that now.