User Profile
Al_kp
Copper Contributor
Joined Nov 26, 2019
User Widgets
Recent Discussions
Re: Make Excel use a text string as a formula
trdebruin Thanks Tim. This works but it requires the referenced workbook to be open. As most instances of this will reference 12 separate books (monthly reporting) this isn't practical. The best solution I've come up with to date is to save hyperlinks to the separate books to enable them to be quickly opened with just 12 clicks, but it's not a great work around.32KViews0likes0CommentsMake Excel use a text string as a formula
I've been trying various ways to quickly generate some data I require that involves dynamically linking various workbooks. This question is based on my latest idea. Is there a way to tell Excel to use text held in a cell as a formula? So for example - A1 holds the text "='C:\DATA\Results " B1 holds the text "Jan 19" C1 holds the text ".xlsx]Sheet 1'!$C$36" I want the excel to action ='C:\DATA\Results Jan 19.xlsx]Sheet 1'!$C$36 and return the value of that cell. I want this to be automatic with no input from the user. What I am trying to achieve is more complicated, but this is the core issue; how to make excel action a formula based on text from multiple cells without requiring manual intervention. I know I can use "&" to concatenate the above then paste it as values, but I'd need to do this hundreds of times). Any help is appreciated.33KViews0likes2CommentsReference or fill series for part of a file location?
Hi, I'm frequently analysing data that involves pulling it from 12 separate monthly files, so a table with data labels vertically and 1 column per each of the 12 months horizontally. I will typically create my formulas for the first month to pull in whatever data I need and copy these across to the other 11 months. I then need to change, 11 times, the location and file being referenced from "Mydata\Jan 19\Data file Jan 19.xlsx" to "Mydata\Feb 19\Data file Feb 19.xlsx", and Mar, Apr, etc. At the moment I do this with the Replace function, which is tediously slow. For any other look up I can get Excel to complete the series, moving the cell references across with the data. I can't figure out how to either get the text of the file location and name to be treated as a series MMM and increment, or to reference another cell that holds the text I want, eg ""Mydata\"&A1&" 19\Data file "&A1"&" 19.xlsx" Any ideas? I just want to be able to drag cross, or pre-set up something that allows my data location to increment by a month as you move along the columns.759Views0likes1Comment
Recent Blog Articles
No content to show