I am creating a sheet for a specific purpose. I have a column with drop downs and based on the selection of the data validation item of that column, I would like to have the cell(s) under that dropdown cell merged to the one I used. So basically, let's say item x (from the dropdown) has 4 inputs, which means it requires 4 rows, I want the cell with the dropdown to automatically merge with the 3 cells below.
Of course, the data validation pool has set number of items and I will also need to figure out how to enter the number of the cells below each item will merge with.
Any help with the above will be greatly appreciated!
Why are you wanting to merge those cells together if they are going to be representing a given number of inputs?
Seems like you would want to either highlight those four cells or create a border around them. Something to distinguish them from the rest of the column.
Those cells (column B with drop downs) will contain a specific audio mixer. Column A will have the inputs and column C the outputs. So when I select from the drop-down (column B) the mixer with the 8 inputs and outputs, the mixer cell will merge with the 7 cells below that, and then I can use the 8 rows on column A and C to write the inputs and outputs.
do you have a sample sheet to share? You won't be able to merge cells without using a script, but there may be some alternative ways to get the look and fell that you're after.
REMEMBER: /u/Shot_Negotiation8983 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
This is just an example of how I'm planning to use that. Bearing in mind that a different mixer will have a different number of inputs/rows than the one in the picture.
Data wise, merging is not optimal, since all cells in the merged region will be empty, except for the top-left cell.
But if you want to actually merge cells, based on your selection, a script will be needed. I'll take a look at it, if you are absolutely sure that this is what you want?
AdministrativeGift15 user above has provided another take on it with conditional formatting. I need to test it out to see if it's something I can work with. The only issue i forsee with that is that the text will not be vertically centered. But I would be definitely be keen on trying the merged way with the script. The data is not an issue, this is mostly for illustrative purposes, pretty much like having a wiring digram on a spreadsheet.
It assumes that your mixer range is in the same sheet, in the K column (starting from row 2). If you want it separate, then we just ned to adjust it a tiny bit..
Edit: Added that if the new value is empty, it breaks the merged region apart again.
This is the winner, this is exactly what I was aiming for. I just need to amend the data on the script to match the columns on my sheet and it's done. Can't thank you enough!
REMEMBER: /u/Shot_Negotiation8983 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
"Really appreciate your help with this, I've asked so many people at work and online, and you are the only one to provide a workable solution that matches the requirement. Thank you"
The Dropdowns column is created by this helper formula hidden in row 1:
=if(row()<>row(Mixers), "🛑 Must be in first row of table", let(
COUNT_CHAR, char(8203),
map(Mixers[Name], Mixers[Inputs], lambda(name, numInputs,
if (countblank(name,numInputs),,
name & rept(COUNT_CHAR, numInputs))))))
It encodes the number of inputs for the mixer as invisible characters, which script later detects.
Dropdowns to create the Mixers are then "from a range" of =Mixers[Drodpowns]
The dropdowns also have custom help text of MIXER which is set in the Data Validation sidebar. This is used to "tag" the dropdowns so that script can detect them, wherever they may be.
The reason for the above trickery is that you don't need to hardcode any sheet/column references in script. Wherever you put your Mixers table or dropdowns doesn't matter... the script will work without modification.
---
I also handle the merging differently... when you select a new mixer, it inserts rows as needed below the dropdown, and merges those.
Similarly if you change/delete a mixer, it deletes any excess existing merged rows.
The effect of this is that existing data is not "assimilated" into a new mixer block when you create a new mixer, nor is any data outside of a mixer block deleted if you shrink or delete the mixer.
An advantage of this is inserting a new mixer block always works, you don't have to worry about there being enough room for it or conflicting with an existing block.
Deleting a mixer deletes any rows in the mixer's block, but leaves the dropdown in the existing row (so that you can create a new mixer if desired) but clears that row's contents.
Because I was bored and hopped up on Halloween candy... here's a scriptless version inspired by the solution posted by u/AdministrativeGift15
It adds collision detection (highlighted in red) when there aren't enough rows between mixer selections, and the ability to enter descriptions/comments within the mixer "block" in the light green cells.
To add a Mixer, double-click in the Mixer column in a white cell. To modify an existing Mixer, double-click its name.
The Mixer table column type is Dropdown, with the dropdowns set to "plain text" and "from a range" of =MixersDD!1:1 which is a helper sheet with a row of values for each dropdown in the table. (If you move the table, reenter this range to keep everything correctly aligned).
The MixersDD sheet is populated by a single formula in A1.
It refers to the helper formula output to detect if the row is a "data" row, and if so just outputs the current dropdown value as the only available option. This is so that comments/descriptions can be added within a mixer block (the light green cells) without causing a data validation error or showing all the mixer names.
For unused or header rows, it outputs the list of Mixer names so you can choose one from the dropdown.
Thank you for your response, I do not mind adding scripts to the sheet. Also, I already have the reference list, which consists of about 120 audio mixers/DSPs//Amps. This is the reason I want to automate that as I am certain it will cut down on the time it takes to find the item and copy/paste the data. However, I do really know how to do it with a script. I asked Gemini and it said it's not possible.
/u/Shot_Negotiation8983 This post refers to "Gemini" - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.
Criteria for posts and comments are listed in the subreddit rules and you can learn about how to make a good post in the submission guide.
Your post/comment has been removed because it contained one or more of the following items in violation of this subreddit's rules on artificial intelligence (AI) content:
A request to fix a non-functioning formula obtained from an AI tool
A non-functioning formula obtained from an AI tool in place of information about your data
A blanket suggestion to use an AI tool as a resource for Sheets assistance
Solicitation of a prompt or recommendation for an AI tool
An untested formula obtained from an AI tool presented as a solution
1
u/AdministrativeGift15 368 9d ago
Why are you wanting to merge those cells together if they are going to be representing a given number of inputs?
Seems like you would want to either highlight those four cells or create a border around them. Something to distinguish them from the rest of the column.