Nathan Asius
New Member
- Joined
- Jan 15, 2024
- Messages
- 41
- Office Version
- 365
- Platform
- Windows
I have a large table of prices. The column headers in the top row have titles with some duplicates which can't be changed. I can't break up the table into more than one.
Those column headers in the top row also are used to produce a validation list. But that list first shows up in a spill list on another sheet before it gets pulled into the drop down on the user's main dashboard. (As shown in this video from Up4Excel on Unique Lists
). The Drop Down List does not get pulled directly from the header Row of the table.
Furthermore, I have more than a hundred formulas in various places in my workbook that lookup prices in the table through Index/Match formulas. So not much can be done to change to structure or naming of the table fields.
The goal is to get rid of the number that Excel automatically produces in the column header. ie Apples, Apples2, Apples3.
The only solution I came up with was to change the color of that number to white. It didn't appear in the Header now, but it still shows up in the drop-Down list.
So I think I need a formula that will do just that. I've never known a formula being even close to changing a font color. So I'm at a loss here or requiring VBA coding.
I have other column headers in the formula that also have numbers. So the formula needs to do the unique thing of finding a single digit number at the end position of a string of letters without a space in-between, and then change the font color to white. Any other number appearing in these column headers, has spaces around them.
So it will find Apples2 and Apples3 and change those numbers' font color to white. But not do the same when finding Apples 2, Bananas 8, 16 Pecans, or Candy 7 Corn.
Is this possible? Or is there a better solution?
My spill list shows up in D4. And repeats for 8 rows. to D12. ( $D4:$D12 )
The header row has been defined into 4 named ranges: Fruit, Beans, Nuts and Candies.
My Drop Down list appears in another Sheet : Sheet10! E14
Thank you.
Those column headers in the top row also are used to produce a validation list. But that list first shows up in a spill list on another sheet before it gets pulled into the drop down on the user's main dashboard. (As shown in this video from Up4Excel on Unique Lists
Furthermore, I have more than a hundred formulas in various places in my workbook that lookup prices in the table through Index/Match formulas. So not much can be done to change to structure or naming of the table fields.
The goal is to get rid of the number that Excel automatically produces in the column header. ie Apples, Apples2, Apples3.
The only solution I came up with was to change the color of that number to white. It didn't appear in the Header now, but it still shows up in the drop-Down list.
So I think I need a formula that will do just that. I've never known a formula being even close to changing a font color. So I'm at a loss here or requiring VBA coding.
I have other column headers in the formula that also have numbers. So the formula needs to do the unique thing of finding a single digit number at the end position of a string of letters without a space in-between, and then change the font color to white. Any other number appearing in these column headers, has spaces around them.
So it will find Apples2 and Apples3 and change those numbers' font color to white. But not do the same when finding Apples 2, Bananas 8, 16 Pecans, or Candy 7 Corn.
Is this possible? Or is there a better solution?
My spill list shows up in D4. And repeats for 8 rows. to D12. ( $D4:$D12 )
The header row has been defined into 4 named ranges: Fruit, Beans, Nuts and Candies.
My Drop Down list appears in another Sheet : Sheet10! E14
Thank you.
