excel help

[shirt]

i am making a spreadsheet with drop down boxes in the columns.

say column A is a category heading with category A1, A2, A3.

B is a sub category columns. A1, A2 and A3 would have completely different sub categories.

how do i make it such that if you make a selection in column A, the drop down in column B then only shows the sub categories for that selection?

[Wilmslowboy]

This is exactly the kind of question you ask Chatgpt or Copilot

I copied and pasted your question in and this was the respose (not tested it).


To achieve this in Excel, you need to use **Dependent Drop-down Lists** with **Named Ranges** and **Data Validation**. Here’s how you can do it:

---

1. # **Step 1: Set Up Your Data**

Create a reference table for categories and subcategories.

| Category | Subcategories A1 | Subcategories A2 | Subcategories A3 |
|----------|-----------------|-----------------|-----------------|
| A1 | SubA1_1 | SubA2_1 | SubA3_1 |
| | SubA1_2 | SubA2_2 | SubA3_2 |
| | SubA1_3 | SubA2_3 | SubA3_3 |

---

1. # **Step 2: Define Named Ranges**

1. Select the **A1 subcategories** (e.g., cells B2:B4) and name this range as **A1**.
2. Select the **A2 subcategories** (e.g., cells C2:C4) and name this range as **A2**.
3. Select the **A3 subcategories** (e.g., cells D2:D4) and name this range as **A3**.

To name a range:
- Select the range.
- Go to **Formulas** > **Name Manager** > **New**.
- Enter the name (must match the category name exactly).
- Click **OK**.

---

1. # **Step 3: Create Drop-down in Column A**

1. Select the cells in **Column A** where you want the category drop-down.
2. Go to **Data** > **Data Validation**.
3. In **Allow**, choose **List**.
4. In the **Source**, enter:
```
A1, A2, A3
```
5. Click **OK**.

---

1. # **Step 4: Create Dependent Drop-down in Column B**

1. Select the cells in **Column B** where you want the subcategory drop-down.
2. Go to **Data** > **Data Validation**.
3. In **Allow**, choose **List**.
4. In the **Source**, enter this formula:
```
=INDIRECT(A2)
```
- This assumes **A2** is the first row where the category selection is made.
- Adjust based on your starting row.

5. Click **OK**.

---

1. # **How It Works**

- When you select **A1** in Column A, Column B will only show subcategories for **A1**.
- If you select **A2**, it will show only **A2**'s subcategories, and so on.

Let me know if you need help refining this! ??

Edited by Wilmslowboy on Sunday 9th February 17:18

[shirt]

i didn't think of that, thanks.

now i know what its called i found a YT vid and sorted it.

only now i want things that need VB. i think i'm better off finding someone of fiverr and just getting them to set it all up for me!

[Xenoous]

I've done this exact thing when building a basic asset list for company I work for. Hope you got it sorted. As mentioned, it's done by creating named boxes.

[shirt]

yeah i did that bit but then got carried away and now want additional things. found a guy on fiverr who will do it for $50 so it can't be that hard...

its a basic document library. i want one sheet where you enter a supplier document number and it searches to check if its there already.

if so i want a prompt to appear for them to check the entry in the library and check for revision control.

if not then the person selects a set of drop down boxes which creates a doc. no. based on those selections, and allocates the next available sequential end no. click a button and it adds it as a new entry in the library.

kind of thing that should be easy if i knew how but i don't and it's worth $50. i downloaded a few templates for $10 and they're no good. same for action tracking dashboards so there's more work for the guy.