Dynamic range column reference

[tuna666]

Hello everybody,

I hope everyone is well today. I've come to ask for your help once again with a function(s) and formulae that I know very little of.

Look at the attached sample worksheet attachment below. You will see two sets of data, each bordered off with blue and red borders. If you look at the blue border, my issue is this: I want to be able to return a column reference that returns full columns ("Product A", "Product B", "Product C" and Product D") minus the the empty columns...you know, a dymanic range but with columns.

To help clarify this, look at the data in the red column. If you go to 'Formulas' and select 'Name Manager' under Defined Names, you will see that there is a range called "Example_List" and if you look at it reference, it has this formula which allows it to be a dymanic range of the yellow shaded area. So when referring to the "Example_List", all empty blank cells are dynamically excluded from the list.

I'm looking for a formula that with do precisely this, but with the range being the columns in the blue border. So I want to be able to dynamically return all columns that are not empty with a formula. Is this possible? And is so, please provide an example?

Sample sheet.xlsx

With kind regards,
Chris

[tuna666]

Oh and before you ask where I want to return the columns to, returning to a new sheet will be fine ^_^ Thanks

[XOR LX]

Hi,

I'm a litte confused here, as I can't see how your desired output ties in with the example you give of an offset, named range.

Are you simply wanting to create a similarly dynamic, named range which encloses all data in columns B-J which is not empty (e.g. in your attachment this would be B4:E56)?

If not, and you simply want formula(e) in another sheet to bring in all data from non-blank columns within this range, then why the named range example? Do you require the named range as an added benefit to any proposed solution, or are you insisting upon it?

Regards

[tuna666]

I mean that Im looking to be able to use an offset array formula that returns all the columns to a new spreadsheet and excludes the empty columns. So that when you fill in or delete data in columns, the columns with data in eill be returned to the new sheet and not the empty ones.*The examples that I put was just an example of something simpler but in the form of a nsmed range for a dropdown list. Sorry if that confused you. I hope this clarify things a bit

[tuna666]

Xor, yes. I am simply looking for a formula in another sheet to bring in all data from non-blank columns within this range. I want to use this formula within the OLSReg function formula. Do you think you could help?

Hi,

I'm a litte confused here, as I can't see how your desired output ties in with the example you give of an offset, named range.

Are you simply wanting to create a similarly dynamic, named range which encloses all data in columns B-J which is not empty (e.g. in your attachment this would be B4:E56)?

If not, and you simply want formula(e) in another sheet to bring in all data from non-blank columns within this range, then why the named range example? Do you require the named range as an added benefit to any proposed solution, or are you insisting upon it?

Regards

[XOR LX]

Hi,

Apologies. If it's a VBA solution you're after then I won't be able to help, though I trust someone with expertise in that area will pick up on this thread shortly.

Reards

[tuna666]

Hehe no its not a VBA solution I am after. Sorry for the confusion

[XOR LX]

Are columns always returned to a column which matches their position in the original sheet? Or, if for example you had columns B and E non-empty, but columns C and D empty, the new sheet would return columns B and E adjacent in...which columns on the new sheet? A and B?

Regards