Reference other cells based on value from dropdown list
I have a sheet in which I store information about many variables (call this the reference sheet). I then have another sheet where I want to populate a column based on IDs from this other sheet, and then reference the other columns in the sheet depending on which ID is selected (either directly by pulling the values in - this may be easiest, or indirectly by using the values in a formula).
Example:
Reference Sheet:
What I'd like to create:
Where the id "C" was created using Data Validation from the Reference Sheet ID column. What I want is for Var1 - Var3 to be created by referring to the row in the reference sheet corresponding to "C". Alternatively, a calculation (in this case it's the sum of var 1 + var 2 divided by var 3) based on these values could be done in this sheet. Note: for this example, I just copied the output I want - but I do not know how to create this dynamically (which is my question).
Is this something that is possible in Excel? If so, how would I do that?
Assuming that the reference sheet is called sheet1, you can put this formula in the other sheet (in cell B2): =INDEX(Sheet1!$B$2:$D$6,MATCH($A2,Sheet1!$A$2:$A$6,0),MATCH(B$1,Sheet1!$B$1:$D$1,0)). Use the fill handle to copy the formula to the cells to the right (until D2).
And this formula in cell E2: =(B2+C2)/D2.
- How to make picture %60 transparency in vba?
- Draw vertical line in scatter plot?
- Special character check mark
- How to change Font Color of Dates without changing color of other text or background using VBA for Excel
- Update cell value based on another cell VBA
- Use Console as debug window in VBA
- Calculate median using vba
- How can I join cells with a vertical line?
- How to change font color for specific line (row) of Textbox
- How do i use Excel Random list generation without duplicate
- excel countif entire row in a table
- Simplifying If Statements Into Loop
- What is the equivalent of the "Days" function or number of days between 2 dates in excel 2007?
- How to find and replace with regex in excel
- How to format graph to show dates from one or more sheets on the horizontal axis?
- How to embed a SVG into a Cell using Aspose.Cells
- Run-Time Error 91 (object variable or with block variable not set) on VBA Email code "mailItem.Display" after IF statements
- loop through excel raw data file and input them to other excel formula sheet and perform iteration calculation
- Deleting duplicate header rows
- mixed date converted from default mm/dd/yy in excel to dd/mm/yy
- Overwrite Excel 2016 worksheet data with data from CSV file
- Laravel validator and excel files error
- Need help aligning a table to a column based on similar values in one table field
- Index rows and columns unexpected results
- Ascii Art using VBA
- How to obtain the full-path names of files read with WorkbookConnections
- How can export all part of columns to pdf file with VBA code?
- Difficult to run
- Allow User Input in Empty Cells, Prevent Editing or Deletion in Occupied Cells
- Export Charts from Excel as images using Python