Mar 27 2024 08:28 AM
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)).
Mar 27 2024 08:46 AM
SolutionTo 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:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0))
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:
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.
Mar 27 2024 08:49 AM
Mar 27 2024 11:19 AM
@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.
Mar 27 2024 09:58 PM
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:
Here's a step-by-step guide:
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:
Mar 28 2024 01:15 PM - edited Mar 28 2024 01:26 PM
@NikolinoDE I have the formulas working, I may have not explained what i am actually needing very well. When I get the retun value that we are needing after the subtraction, That valve needs to update the table with that specfic valve. Example: Table shows 24 the new value is 19, I need 19 to be in the table so if we have another entry for that Unit and day it take away from the 19.
Mar 29 2024 08:30 PM
It seems that you are expecting a variable number of downtime records (per unit, per day). A combination of INDEX and MATCH is good for returning a single cell/row/column, but for a variable number, the FILTER function is usually required.
See the attached workbook. I have included notes, and URLs of documentation, on the _Info worksheet.
Mar 27 2024 08:46 AM
SolutionTo 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:
=INDEX(TableRange, MATCH(UnitSelection, UnitColumn, 0), MATCH(DaySelection, DayRow, 0))
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:
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.