Forum Discussion
Excel formula Help
Hi all,
I have an interesting project I am working on, In the image below what we are needing to do is be able to select from a drop down the "Unit" then the "Day" to select the cell for the particular day and unit then on the downtime subtract whatever the number is we put in (example: downtime was 4 hours on Unit 2101 on Day 1 would = cell C3 (20)).
To achieve the desired functionality, you can use a combination of the INDEX and MATCH functions along with the dropdown menus. Here's a step-by-step guide on how to set it up:
- Create Dropdown Menus:
- Create dropdown menus for selecting the "Unit" and "Day" values. You can use Data Validation to create these dropdowns.
- Create a Reference Table:
- Create a reference table where you list all the units in one column and all the days in the header row. The intersection of each unit and day will represent the corresponding cell in your worksheet.
- Use INDEX and MATCH:
- In a separate cell, use the INDEX and MATCH functions to retrieve the value from the selected unit and day. Here's the formula:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0))
- TableRange: This is the range of cells where your data is located.
- UnitSelection: This is the dropdown cell containing the selected unit.
- UnitColumn: This is the column in your reference table where the units are listed.
- DaySelection: This is the dropdown cell containing the selected day.
- DayRow: This is the row in your reference table where the days are listed.
- Subtract Downtime:
- Once you have the cell reference, you can subtract the downtime value from it. Let's say the downtime value is entered in another cell, you can subtract it using a simple subtraction formula.
Here's an example of how your reference table might look:
| Day 1 | Day 2 | Day 3 | Day 4 | ...
-------------------------------------------
Unit 1 | 10 | 15 | 20 | 25 | ...
Unit 2 | 12 | 18 | 22 | 27 | ...
Unit 3 | 11 | 16 | 21 | 26 | ...
... ... ... ... ...
And here's how the formula might look in Excel:
=INDEX($C$2:$F$10, MATCH(B2, $B$2:$B$10, 0), MATCH(A2, $C$1:$F$1, 0))
In this formula:
- $C$2:$F$10 is the range of cells containing your data.
- B2 is the cell containing the selected unit.
- $B$2:$B$10 is the range of cells containing the units.
- A2 is the cell containing the selected day.
- $C$1:$F$1 is the range of cells containing the days.
This formula will return the value from the corresponding cell based on the selected unit and day. You can then subtract the downtime value from this cell. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
6 Replies
To achieve the desired functionality, you can use a combination of the INDEX and MATCH functions along with the dropdown menus. Here's a step-by-step guide on how to set it up:
- Create Dropdown Menus:
- Create dropdown menus for selecting the "Unit" and "Day" values. You can use Data Validation to create these dropdowns.
- Create a Reference Table:
- Create a reference table where you list all the units in one column and all the days in the header row. The intersection of each unit and day will represent the corresponding cell in your worksheet.
- Use INDEX and MATCH:
- In a separate cell, use the INDEX and MATCH functions to retrieve the value from the selected unit and day. Here's the formula:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0))
- TableRange: This is the range of cells where your data is located.
- UnitSelection: This is the dropdown cell containing the selected unit.
- UnitColumn: This is the column in your reference table where the units are listed.
- DaySelection: This is the dropdown cell containing the selected day.
- DayRow: This is the row in your reference table where the days are listed.
- Subtract Downtime:
- Once you have the cell reference, you can subtract the downtime value from it. Let's say the downtime value is entered in another cell, you can subtract it using a simple subtraction formula.
Here's an example of how your reference table might look:
| Day 1 | Day 2 | Day 3 | Day 4 | ...
-------------------------------------------
Unit 1 | 10 | 15 | 20 | 25 | ...
Unit 2 | 12 | 18 | 22 | 27 | ...
Unit 3 | 11 | 16 | 21 | 26 | ...
... ... ... ... ...
And here's how the formula might look in Excel:
=INDEX($C$2:$F$10, MATCH(B2, $B$2:$B$10, 0), MATCH(A2, $C$1:$F$1, 0))
In this formula:
- $C$2:$F$10 is the range of cells containing your data.
- B2 is the cell containing the selected unit.
- $B$2:$B$10 is the range of cells containing the units.
- A2 is the cell containing the selected day.
- $C$1:$F$1 is the range of cells containing the days.
This formula will return the value from the corresponding cell based on the selected unit and day. You can then subtract the downtime value from this cell. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
NikolinoDE I have the formula working, but how do I return the subtracted number back into the table. I forgot to mention that the selections and the downtimes are running numbers so the table may need to update several times with several different entries on the same day and unit.
To return the subtracted number back into the table, you can use the same INDEX and MATCH functions, but this time to locate the specific cell where you want to input the subtracted downtime. Here's how you can do it:
- Identify the Cell to Update:
- Use the same INDEX and MATCH functions to identify the cell in the table where you want to input the subtracted downtime. This will be the same formula you used to retrieve the original value.
- Subtract Downtime:
- Subtract the downtime from the original value retrieved by the INDEX and MATCH functions. You can do this within the same formula or in a separate cell.
- Update the Table:
- Use the INDEX function again to locate the cell in the table where you want to input the subtracted downtime, and set its value to the result of the subtraction.
Here's a step-by-step guide:
- Identify the Cell to Update:
- Use the same INDEX and MATCH functions you used to retrieve the original value. This will give you the reference to the cell in the table that needs to be updated.
- Subtract Downtime:
- Subtract the downtime from the original value. Let's say the downtime value is entered in cell D1, and the original value is retrieved in cell E1. You can use a formula like =E1 - D1 to calculate the subtracted value.
- Update the Table:
- Once you have the subtracted value, use the INDEX function again to locate the cell in the table where you want to input the subtracted downtime. For example, if the original value was retrieved in cell E1, you can use the same INDEX function to locate the corresponding cell in the table and set its value to the subtracted value.
Here's an example of how the formula might look:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0))
And here's how you can modify it to update the table with the subtracted downtime:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0)) - DowntimeValue
In this formula:
- TableRange, UnitSelection, UnitColumn, DaySelection, and DayRow are the same as before.
- DowntimeValue is the cell reference where the downtime value is entered.
- Thank You I will input the formulas and let you know if i need any additional help.
