I should mention in advance that my knowledge is scattered at best and I don't actually have access to any form of excel as I type this to recall/test, so I apologize if i'm at all unclear/use awkward terminology.
I'm exporting a large amount of information into an excel document(DocA i'll now call it). It's VERY hard to quickly parse in this format, so i figured i'd make another excel file(DocB) that would pull data from specific cells on DocA and display it in a much easier to manipulate format. DocB would be a completely separate file because i may have different DocA's in the future which would have the same formatting, but different data and i'd like to just be able to save over the old one and have DocB still grab all the relevant data(NONE of the cells will change position. Only value)
For example, on DocA I want data from the following cells-
H9
H40
H71
etc. Incrementing by 31's on until X(could be only a few, could possibly be in the thousands).
And i'd like to display in DocB's
A1
B1
C1
etc.
The method i tried to use was:
copying DocA:H9, pasting it with link to DocB:A1
copying DocA:H40, pasting it with link to DocB:B1
Selecting DocB cells A1 and B1
Grabbing the box in the lower right hand corner and dragging it out as far as needed.
I'd hoped it would just continue the formula increments, but instead it just repeats(C1=H9, D1=H40, etc). Google somewhat lead me in circles so I thought i'd ask here for help before i continue. I can think of a couple of awkward brute force methods, but I'm hoping there's a quicker/easier/more elegant way as I don't want to spend a day linking 600 cells. I know this can be done with increments of 1, but the fact that it's jumping by 31's and referencing another document completely is giving me trouble.
Further to kill two birds with one stone-
How can I get a formula to actually replace itself with the value it pulls?
Example-
C1: =A1&" "&B1
Usually i actually have to select cell C1 and hit F9, or copy the cell and paste values somewhere else so i can actually manipulate the data, not the formula. Is there a way to have a formula set to do this automatically?
I'm exporting a large amount of information into an excel document(DocA i'll now call it). It's VERY hard to quickly parse in this format, so i figured i'd make another excel file(DocB) that would pull data from specific cells on DocA and display it in a much easier to manipulate format. DocB would be a completely separate file because i may have different DocA's in the future which would have the same formatting, but different data and i'd like to just be able to save over the old one and have DocB still grab all the relevant data(NONE of the cells will change position. Only value)
For example, on DocA I want data from the following cells-
H9
H40
H71
etc. Incrementing by 31's on until X(could be only a few, could possibly be in the thousands).
And i'd like to display in DocB's
A1
B1
C1
etc.
The method i tried to use was:
copying DocA:H9, pasting it with link to DocB:A1
copying DocA:H40, pasting it with link to DocB:B1
Selecting DocB cells A1 and B1
Grabbing the box in the lower right hand corner and dragging it out as far as needed.
I'd hoped it would just continue the formula increments, but instead it just repeats(C1=H9, D1=H40, etc). Google somewhat lead me in circles so I thought i'd ask here for help before i continue. I can think of a couple of awkward brute force methods, but I'm hoping there's a quicker/easier/more elegant way as I don't want to spend a day linking 600 cells. I know this can be done with increments of 1, but the fact that it's jumping by 31's and referencing another document completely is giving me trouble.
Further to kill two birds with one stone-
How can I get a formula to actually replace itself with the value it pulls?
Example-
C1: =A1&" "&B1
Usually i actually have to select cell C1 and hit F9, or copy the cell and paste values somewhere else so i can actually manipulate the data, not the formula. Is there a way to have a formula set to do this automatically?
