SOLVED

Copying data that changes location on a worksheet

Copper Contributor

Hello Excel folks.

 

I am hoping for some VBA help.  I am needing to copy data, daily, from a report generated as a worksheet.  That data is always in teh same location relative to the data label (see example) but the row is seldom if ever the same each time the report is run. The label will always be Column B and the needed  value will always be Column M.   In the example I need to copy the value of 2.64 to another sheet workbook and populate a cell next to "Apples"  

 

Any help will  be GREATLY appreciated

 

 

Copy from hereCopy from here

Copy from here

 

Copy to hereCopy to here.

Copy to here

 

Thanks

JJ

9 Replies

@jjelliott 

 

Well, some questions to define this situation a bit more fully. Personally, I'm hoping that VBA is not what you need. I'm generally of the opinion that the more elegant solutions always take advantage of the amazing pre-defined functions/formulas that Excel offers. But there are times when the brute force methods have to be used, so I won't be absolutist about it.

 

  1. Is the label in your source document always one row above the summary row?
  2. Does the word (to stay with your example) "Apples" only appear once, also in that row above the summary row?
  3. (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?)

@mathetes 

 

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.

 

  1. Is the label in your source document always one row above the summary row?  Yes
  2. Does the word (to stay with your example) "Apples" only appear once, also in that row above the summary row? Yes
  3. (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.

 

best response confirmed by jjelliott (Copper Contributor)
Solution

@jjelliott 

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?

@jjelliott 

 

How about incorporating a use of INDIRECT to refer to the new file name (Report (1)...etc) . 

INDIRECT can be tricky to master, but once you've gotten it, it gives a lot of flexibility.

https://exceljet.net/excel-functions/excel-indirect-function

 

@mathetes you are going to get me to learn this stuff if it kills me! My initial fix was far less elegant. I wrote VBA to copy the formulas to the source sheet, populated the values, then copied the data back to the destination sheet. Indirect is much cleaner. Thank you again!
I often compare the use of VBA and macros to using brute-force to accomplish things. It's not that they're never useful, sometimes the only way to accomplish certain tasks, but so often there are functions and formulas that will do things both more elegantly and efficiently, but also [very important] more reliably.
1 best response

Accepted Solutions
best response confirmed by jjelliott (Copper Contributor)
Solution

@jjelliott 

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

View solution in original post