←Back to Beyond Market Intelligence
•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Building a dynamic formula

Our take

Navigating the complexities of Excel can be challenging, especially when trying to predict storage device capacity. You've attempted to streamline your process with dynamic formulas using "concat," only to discover that it merely constructs the formula without evaluating it. While "indirect" seemed like a solution, it falls short when dealing with external workbooks that aren't open. This leads to frustrating "REF" errors.

I'm using Excel to predict the capacity usage of storage devices. It's clunky, but it works great until someone changes the name of the SharePoint folder I'm using to reference my data. (This has happened twice) I had the brilliant idea (or so I thought) to dynamically build the formula using "concat", which I thought would allow me to simply change one cell and be able to affect all of my entries (about 65 rows).

I then learned that "concat" only builds the formula, it does not evaluate it and that I needed to use "indirect" to evaluate it. That worked, except my output was "REF", not what I was expecting. After a little more digging, I found that "indirect" does not work with external workbooks that are not open on the local machine. (I don't want to open 60 workbooks to get the data I need)

It looks like I am out of luck, but I am hoping someone else has a brilliant idea I have not considered yet.

Thanks

submitted by /u/Separate-Tomorrow564
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Tagged with

#Excel alternatives for data analysis#AI formula generation techniques#rows.com#formula generator#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#machine learning in spreadsheet applications#financial modeling with spreadsheets#Excel#dynamic formula