Make My Excel Dropdown a Little Less Dynamic
June 28, 2022 10:38 AM Subscribe
I am making a workbook in Excel that has multiple sheets. On the first sheet, which is called "Hierarchy" I list categories for companies. We'll call them A, B, C, D, and E. They are in cells A4:A8. On the next sheet "Companies" I list companies, with a column for assigning them into one of those categories. I need to populate a dropdown for that with the values I created on the previous page. I'm stuck though, because although I can do this swimmingly for the first row, I do not know how to get the exact same values to show up in rows below that.
How can I get this dropdown to appear in every row without dynamically changing its dropdown choices?
Currently, I can use Data Validation to pull a list based on a table on the Hierarchy page. However, when I try to copy and paste that dropdown (which uses that A4:A* range) it adds a digit to both the start and stop point, meaning the second row gets a range of A5:A9, the third one of A6:A10, and so on. I don't want the cells it draws from on the table to change. I want them exactly the same, over and over.
How do I accomplish this?
How can I get this dropdown to appear in every row without dynamically changing its dropdown choices?
Currently, I can use Data Validation to pull a list based on a table on the Hierarchy page. However, when I try to copy and paste that dropdown (which uses that A4:A* range) it adds a digit to both the start and stop point, meaning the second row gets a range of A5:A9, the third one of A6:A10, and so on. I don't want the cells it draws from on the table to change. I want them exactly the same, over and over.
How do I accomplish this?
Best answer: Use "$A$4:$A$8" instead of "A4:A8".
posted by Jawn at 10:42 AM on June 28, 2022 [3 favorites]
posted by Jawn at 10:42 AM on June 28, 2022 [3 favorites]
For future education, the two answers are subtly different.
A4 will change to a different column if moved horizontally, and will change to a different row if moved vertically.
$A4 will always refer to Column A, but will change to a different row if moved vertically.
A$4 will change to a different column if moved horizontally, but will always refer to Row 4.
$A$4 will always refer to A4, no matter how it is moved.
Each of these are useful in different circumstances, depending on the specific calculations needed and how the data is arranged.
posted by yuwtze at 12:34 PM on June 28, 2022 [4 favorites]
A4 will change to a different column if moved horizontally, and will change to a different row if moved vertically.
$A4 will always refer to Column A, but will change to a different row if moved vertically.
A$4 will change to a different column if moved horizontally, but will always refer to Row 4.
$A$4 will always refer to A4, no matter how it is moved.
Each of these are useful in different circumstances, depending on the specific calculations needed and how the data is arranged.
posted by yuwtze at 12:34 PM on June 28, 2022 [4 favorites]
When I'm using list-based Data Validation, I always define a range name for the list of values, e.g. the name lstCategories for A4:A8. Then when defining the list for Data Validation, enter "=lstCategories". When you copy and paste the drop-down, it will keep on referencing the lstCategories range, with no need for you to tweak anything, and it makes the whole design clearer for anyone who uses it
posted by kelper at 1:19 PM on June 28, 2022 [3 favorites]
posted by kelper at 1:19 PM on June 28, 2022 [3 favorites]
« Older Where can I get video of Westminster dog show... | Apply different designs to 3D model of a Headscarf Newer »
This thread is closed to new comments.
posted by adekllny at 10:41 AM on June 28, 2022 [6 favorites]