Forum Discussion
Copying data that changes location on a worksheet
Here's a formula for you to try.
=INDEX([Source.xlsx]Sheet1!$M$1:$M$100,XMATCH("Fruit: Apples",[Source.xlsx]Sheet1!$B$1:$B$100,0)+1)I've made it work in the attached very rough example files.
Basic ideas:
- the XMATCH portion determines the row in column B in which the text "Fruit: Apples" appears
- the INDEX function takes that row, adds 1, and finds the value in the resulting row in column M
You'd have to do some things to adapt it to your situation.
- change the references, both to the actual source files real name, and to the range of rows possible (I use 100 as the max)
- once the value has been retrieved, change it from a formula to a value; this might involve writing a VBA routine, but whether that's needed could depend on the volume of transactions
I am all for the elegant approach. To be more clear, the worksheet that is generated but the 3rd party application is not adjustable at all. It is a report that is run daily and the generated worksheet is then discarded. The one that the data is being copied to (you probably figured this out) remains and is appended daily.
- Is the label in your source document always one row above the summary row? Yes
- Does the word (to stay with your example) "Apples" only appear once, also in that row above the summary row? Yes
- (Parenthetically, let me ask, would it be possible to have the source data changed so that "Apples" also appeared in column B of the summary row?) Sadly, no
Thanks million for the help, manually copy/pasting this daily with a large collection of "fruit" is making me crazy.
Here's a formula for you to try.
=INDEX([Source.xlsx]Sheet1!$M$1:$M$100,XMATCH("Fruit: Apples",[Source.xlsx]Sheet1!$B$1:$B$100,0)+1)
I've made it work in the attached very rough example files.
Basic ideas:
- the XMATCH portion determines the row in column B in which the text "Fruit: Apples" appears
- the INDEX function takes that row, adds 1, and finds the value in the resulting row in column M
You'd have to do some things to adapt it to your situation.
- change the references, both to the actual source files real name, and to the range of rows possible (I use 100 as the max)
- once the value has been retrieved, change it from a formula to a value; this might involve writing a VBA routine, but whether that's needed could depend on the volume of transactions
- mathetes
You would not believe the mess of VBA I had trying to do this.
Your solution worked flawlessly except for the briefly confounding issue of needing to use MATCH rather then XMATCH in Excel 2019.
=INDEX([Report.xlsx]Sheet1!$M$1:$M$1000,MATCH("Skillset: ABCS_FX",[Report.xlsx]Sheet1!$B$1:$B$1000,0)+1)
I cannot thank you enough!
JJ- Glad to hear it.
- mathetes I am running in to one minor albeit annoying issue. Since the workflow is to open the report without saving it and when the source report is generated it is sometimes incremented as report (1).xlsx, report(2).xlsx, etc. because there is a leftover temp file, the external references don't work. Attempts to wildcard it don't seem to work (though I might be doing that wrong). The only solution I can come up with is to do in VBA and run it from the source workbook. Thoughts?