Forum Discussion
Fill out other cells based on selection
Hello all, brainstorming some ideas. Want to make it easier for my colleagues to fill out. Currently for review section I made it into a dropdown list so user can select from a list than typing it out every time.
What i want is based on the selection in 'Review Section', I want it to populate the sample size and total.
I could do a switch statement based on what was chosen but i want to see if there is another easier option. Trying something new, any suggestions are welcomed.
My Org did restrict VBA so I can't use those. ty
Yes you can use SWITCH or you can have a table or Defined Names and use XLOOKUP (or other lookup option if you prefer). So commonly you would have a table either off to the side on this sheet or on another sheet and then use XLOOKUP to find the corresponding value:
=XLOOKUP( A2:A10, Ltable[ReviewSection], Ltable[ReviewSampSize], "")
where A2:A10 are the values they enter from the drop-down in the Review Section, the Ltable[ReviewSection] is the column in the lookup table where the list of Review Sections is located, and the Ltable[ReviewSampSize] is the column in the lookup table where the 'Review Sample Size Total Request' values are located.
Then do the same for the MS Actual Sample Size.
If you prefer not having the lookup table on a sheet (note you could have it on another sheet and 'hide' that sheet) you could just create the Names and assign the array of values to each corresponding Name. But the table is easier to make and maintain.
2 Replies
Yes you can use SWITCH or you can have a table or Defined Names and use XLOOKUP (or other lookup option if you prefer). So commonly you would have a table either off to the side on this sheet or on another sheet and then use XLOOKUP to find the corresponding value:
=XLOOKUP( A2:A10, Ltable[ReviewSection], Ltable[ReviewSampSize], "")
where A2:A10 are the values they enter from the drop-down in the Review Section, the Ltable[ReviewSection] is the column in the lookup table where the list of Review Sections is located, and the Ltable[ReviewSampSize] is the column in the lookup table where the 'Review Sample Size Total Request' values are located.
Then do the same for the MS Actual Sample Size.
If you prefer not having the lookup table on a sheet (note you could have it on another sheet and 'hide' that sheet) you could just create the Names and assign the array of values to each corresponding Name. But the table is easier to make and maintain.
Ty, first time using xlookup. I did a table in another sheet and it worked like a charm. This will also allow me to expand as needed
