Forum Discussion
Copying data that changes location on a worksheet
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 here
Copy from here
Copy to here.
Copy to here
Thanks
JJ
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
9 Replies
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.
- Is the label in your source document always one row above the summary row?
- Does the word (to stay with your example) "Apples" only appear once, also in that row above the summary row?
- (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?)
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
