Combine a Row of Cells with a Dynamic Range?

**an0nymous** · 02-03-2013, 06:54 PM

Hello,

So, this is sort of two questions. I've been looking up ways to do this on the internet, and the impression I got is that I would need to use some kind of VBA to achieve it. Unfortunately, I am not at all familiar with VBA.

What I'm trying to do is this: Combine the contents of a row of cells (issue 1). However, the number of cells that I want to combine changes depending on the value in a separate cell (issue 2).

For example, if cell Q7 holds an 18, I'd want to combine cells C4 through cells T4. However, if cell Q7 holds a 5, I'd want to combine cells C4 to G4. Is this possible? I've tried all these different ways within the sheet itself, but I can't seem to do it.

Thanks so much. Any assistance would be appreciated.

**FDibbins** · 02-03-2013, 07:03 PM

Hi and welcome to the forum

Are the contents of the cells values or text?

If values, you can use offset() indirect() and sum()

Without seeing what you are working with though, I cant suggest what the full formula would look like
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**TMS** · 02-03-2013, 07:07 PM

You could define a Dynamic Named Range, for example:

=Sheet1!$C$4:OFFSET(Sheet1!$C$4,,Sheet1!$Q$7-1)

Regards, TMS

**an0nymous** · 02-05-2013, 02:05 AM

Hi! Thank you guys so much for your responses! I actually found a way around the issues, so I didn't need to go this route. Part of the solution came in the form of this tutorial here, so I just thought I'd link to it in case someone else finds this thread looking for their own solutions. Anyway, I appreciate your prompt help. I'll definitely be back if I run into other issues!

**TMS** · 02-05-2013, 02:12 AM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.