Forum Discussion
Advanced Logical Linking Formulas in Excel
It sounds like you need to create a formula that dynamically selects the route number based on the current date. You can achieve this using a combination of functions like INDEX, MATCH, and logical functions like IF.
Here is a basic example of how you can approach this:
Assuming your dates are in column A and route numbers are in column B, and your current date is in cell S1, you can use the following formula in cell S37:
=IFERROR(INDEX($B$5:$B$34, MATCH(TODAY(), $A$5:$A$34, 0)), "")
This formula will search for the current date (TODAY()) in the range A5:A34 and return the corresponding route number from the range B5:B34. If the current date is not found, it will return an empty string ("").
You may need to adjust the cell references and ranges based on your actual data layout and requirements. Additionally, if you have multiple dates for the current day, you may need to refine the logic further to determine which route number to select.
Feel free to provide more specific details about your data layout and any additional criteria for selecting the current load. The text and steps was created with the help of AI.
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
DELIVERY DAY OF THE WEEK D3:J3
ROUTE NUMBER D5:D34 (SUNDAY)
E5:E34 (MONDAY)
F5:F34 (TUESDAY)
G5:G34 (WEDNESDAY)
H5:H34 (THURSDAY)
I5:I34 (FRIDAY)
J5:J34 (SUNDAY)
CURRENT DATE NOT DISPAYED CURRENTLY
I am trying to make the most current data from the cell areas above (N5:AB35) automatically be populated in the cell area at the bottom (R37:AD66).
I would share the spreadsheet but I cannot upload it here; on a picture.
To achieve this, you can use the following formula in cell R37:
=IFERROR(INDEX($D$5:$J$34, MATCH(TODAY(), $D$1:$J$1, 0), MATCH(TEXT(TODAY(), "ddd"), $D$3:$J$3, 0)), "")
You can then drag this formula across the range R37:AD66 to populate the route numbers for each day automatically. If no matching route number is found for the current date, the formula will return an empty string ("").
- Ok, so I did what you suggested but every cell in the range I pasted the formula above to, reads the same response.
For example, S37 should be searching for the current route number (from the cell ranges: AU5:AU34, BE5:BE34, BO5:BO34, BY5:BY34, CI5:CI34, and CS5:CS34) and returning the CURRENT DATE AND THE FOLLOWING DATE for the corresponding truck # in cell AD37 (pulled from the cell ranges AR5:AR34, BB5:BB34, BL5:BL34, BV5:BV34, CF5:CF34, and CP5:CP34).
I have uploaded a filled in version of this spreadsheet to make it a little easier.
https://1drv.ms/x/s!Atgssv4HR3i6giDu0vSTlYWg8FMl?e=gjWcs6
- You can share the file without data in it, so the people helping you can see the geography of the solution you are trying to develop, the people can create fictitious dummy data for testing.
Yea_So here is the link to a copy of the file. I tried to do this yesterday but this site was not allowing me to submit the reply with the link. Anyhow, here goes:
https://1drv.ms/x/s!Atgssv4HR3i6ghxJdtdGG3ND3Nmt?e=6ytSxjIs this ok?
The value in E5 is looking up the value in A6 in the ,$AR$4:$BA$1048576 range?
you can use onedrive to share things on here so everyone can help you troubleshoot things expeditiously.
