Auto fill cells depending on another cells content

Hi TheShad67​ ,

Just in case you find m_tarler​ excellent answer a bit hard to digest (I notice that you haven't marked it as solved) I've created a sample spreadsheet for you

The spreadsheet has two sheets. Above is the sheet called Gas_Meters with a Named Table created called Meter_Sizes as shown above.

[When you create the table, it will be called Table1 by default, but you can easily rename it as shown above]

The advantage of having a Named Table is that as you add more elements to the table, the Named Table range will expand to accommodate those extra rows.

However, to be able to use a reference to a Named Range in your cell lookup, there is one more trick you have to perform: You have to create a Named Range that refers to the Size column of your Named Table called Meter_Sizes

So the next step is to create that Named Range by selecting cells A2:A3 and clicking Define Name from the Formulas Tab. Give the range the name Meter_Size (as distinct from Meter_Sizes, which is the name of the whole table) and note that the Refers to: field refers to the Named_Table[Column_Name] using the table referencing format - i.e. refers to Meter_Sizes[Size]

You are now ready to tackle cells E2 and F2 back in your main sheet. In my sample, the main sheet is left with the default name of Sheet1.

Now you already have a dropdown defined for cell E2, but if you want that dropdown to be dynamic, make sure it refers to the Named Range that was just created - i.e. Meter_Size.

[Ideally you'd have the lookup refer to Meter_Sizes[Size], but Excel doesn't support table references in dropdowns - hence the whole extra step above to create the Named Range]

And finally, to get the value from the Volume column to appear in cell F6, enter the formula

=XLOOKUP(E2,Meter_Sizes[Size],Meter_Sizes[Volume],"n/a")

into cell  F2 - which will make the corresponding Volume appear next to the size selected in cell E2

You can download my sample file here: https://1drv.ms/x/c/c95331b296c5ed04/IQD4A3dthXZCSZ9ZxQXFDssSAe18GRv6KvAIQwV8FJb48Y0?e=7TiJ9a

HTH

RedNectar