Forum Discussion
Auto fill cells depending on another cells content
I'm OK with Excell, but by no means an expert.
I'm creating a sheet that will calculate gas purge volumes, and I've got most of the cells sorted with calculations based on other cells, but one of them is giving me a headache. It's probably simple for those with experience. So...
I have cell E2 which contains a drop down list (linked to another sheet) which gives options (which are meter sizes) E6, U6, G4, U16, etc. What I'm trying to do is get the column F2 to auto fill depending what is selected in E2. So, if someone selects meter size U6, cell F2 will auto populate with the number 0.0028, if they select meter size E6 then cell F2 will auto populate with the number 0.008, etc
Sorry if the use of E6, U6 etc complicates things, those are the actual names of the meters.
Any help is greatly appreciated.
1 Reply
Sounds like you could use a lookup function. I would suggest creating a lookup table with meter size names and values:
name value
U6 0.0028
E6 0.008
then in column F you use a formula like:
=XLOOKUP(E2, [name column], [value column], "n/a")
for the [name column] and the [value column] those could be cell references like Z2:Z10 or
you could 'name' those ranges like Meter_Names (if you highlight the column of cells then in the upper left the box to the left of the formula entry box you can just type the Name in there or use the Name Manager) or
you could make it a Table (use Home-> Format as Table) and then name it (click on cell in that table and then a Table menu item will show and then all the way on the left you can name that table) and then you can use Structured Names like: MyTableName[name]
