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

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]