Hey everyone! I'm currently interning and stuck on a huge Excel project. I've got a complicated pricing template with multiple tabs, and I need to replicate it for 1,800 other files. The issue is that each of these sheets has a different number of tabs and varying formats.
I've been working on a Python script to automate the process, and I'm facing some challenges with moving complex formulas into the new sheets. Specifically, I want to find where the text "YTD" appears and fill down a formula based on that. However, I think the main issue is that xlwings is having trouble processing the dates correctly.
I've had three years of coding experience and I'm also utilizing AI for help, but I'm a bit lost. If anyone can guide me through this or even look at my script, I'd really appreciate it! I've shared my formula below for reference.
```python
=IF(
Helper!D3 = 0,
SUMIFS(
OrdersHelper!S:S,
OrdersHelper!Q:Q, A18,
OrdersHelper!C:C, Helper!$B$2,
OrdersHelper!A:A, ">=" & TODAY()-365,
OrdersHelper!A:A, "= TODAY()-365) *
(dates <= TODAY()) *
(bolsent = "Yes") *
qty
)
)
)
```
Let me know if you need any specific details on the raw data or any other questions. Thanks!
3 Answers
You mentioned you're testing with just your template and one other sheet? I recommend focusing on getting your formula work in one sheet first before scaling to 1,800 files. Once you have clarity on how to handle dates correctly, you can replicate that logic across other sheets systematically. If the company's data is unorganized, maybe doing a quick cleanup using Python could save future headaches too!
Sounds like a challenging project! Python is a great choice for this, especially with libraries like `pandas` for data manipulation and `openpyxl` or `xlwings` for Excel handling. Since your formulas are failing when you add date criteria, double-check how you are formatting dates in your script. Excel sometimes expects dates in a specific format, and if xlwings is not reading them correctly, that could definitely cause issues. Also, consider simplifying your formula processing into smaller chunks to troubleshoot more easily.
I totally get the struggle with Excel! Have you considered using VBA instead? It's built right into Excel and can handle external references reasonably well. It might streamline the process without the complexity of a separate Python script, plus you wouldn't need to deal with transferring your formulas in a new format.
Related Questions
How To: Running Codex CLI on Windows with Azure OpenAI
Set Wordpress Featured Image Using Javascript
How To Fix PHP Random Being The Same
Why no WebP Support with Wordpress
Replace Wordpress Cron With Linux Cron
Customize Yoast Canonical URL Programmatically