E
E. Miller
I searched previous posts for an answer to my question, but couldn’t find
anything. Please bear with me as I try to explain the situation as clearly
as possible.
Every month a master spreadsheet needs to be updated with data from 6
different sources. The 6 sources submit their updates on spreadsheets that
are copies of the master spreadsheet; I shall call these “subordinate
sheets.†The problem arises when trying to merge the data from the
subordinate sheets with the one master sheet. Every subordinate spreadsheet
has the same basic data. In other words all subordinate sheets have the same
number of rows containing the same information with only the last two columns
blank needing updating. Between subordinate sheets not all cells in the last
two columns have data and different subordinate sheets will have data in
different cells in the last two columns. I have tried using the VLOOKUP
function but quickly discovered that each time a new subordinate sheet is
merged with the master sheet previous data gets over written. Therefore, the
only data on the master sheet is that found on the last merged subordinate
sheet. I also tried using an IF statement in conjunction with the VLOOKUP
function but found that that didn’t work either.
Master Sheet
A B C
1 ID1 Cost 1 Cost 2
2 ID2 Cost 1 Cost 2
3 ID3 Cost 1 Cost 2
4 ID4 Cost 1 Cost 2
5 ID5 Cost 1 Cost 2
6 ID6 Cost 1 Cost 2
Subordinate 1
A B C
1 ID1 Cost 1 Cost 2
2 ID2
3 ID3 Cost 1 Cost 2
4 ID4
5 ID5
6 ID6
Subordinate 2
A B C
1 ID1
2 ID2
3 ID3
4 ID4
5 ID5 Cost 1 Cost 2
6 ID6 Cost 1 Cost 2
Subordinate 3
A B C
1 ID1
2 ID2 Cost 1 Cost 2
3 ID3
4 ID4 Cost 1 Cost 2
5 ID5
6 ID6
Would like to find a way for Excel to check cell B1 of the Master
spreadsheet and if blank or zero go to Subordinate 1 and pull in data from
cell B1. Then check cell C1 of the Master sheet and if blank or zero go
Subordinate 1 and pull data from cell C1. This process would repeat for all
rows in the master spreadsheet. Then Subordinate 2 would be opened and the
process repeated without overwriting data previously updated from Subordinate
1.
Any help, tips or advice would be greatly appreciated.
Eric
anything. Please bear with me as I try to explain the situation as clearly
as possible.
Every month a master spreadsheet needs to be updated with data from 6
different sources. The 6 sources submit their updates on spreadsheets that
are copies of the master spreadsheet; I shall call these “subordinate
sheets.†The problem arises when trying to merge the data from the
subordinate sheets with the one master sheet. Every subordinate spreadsheet
has the same basic data. In other words all subordinate sheets have the same
number of rows containing the same information with only the last two columns
blank needing updating. Between subordinate sheets not all cells in the last
two columns have data and different subordinate sheets will have data in
different cells in the last two columns. I have tried using the VLOOKUP
function but quickly discovered that each time a new subordinate sheet is
merged with the master sheet previous data gets over written. Therefore, the
only data on the master sheet is that found on the last merged subordinate
sheet. I also tried using an IF statement in conjunction with the VLOOKUP
function but found that that didn’t work either.
Master Sheet
A B C
1 ID1 Cost 1 Cost 2
2 ID2 Cost 1 Cost 2
3 ID3 Cost 1 Cost 2
4 ID4 Cost 1 Cost 2
5 ID5 Cost 1 Cost 2
6 ID6 Cost 1 Cost 2
Subordinate 1
A B C
1 ID1 Cost 1 Cost 2
2 ID2
3 ID3 Cost 1 Cost 2
4 ID4
5 ID5
6 ID6
Subordinate 2
A B C
1 ID1
2 ID2
3 ID3
4 ID4
5 ID5 Cost 1 Cost 2
6 ID6 Cost 1 Cost 2
Subordinate 3
A B C
1 ID1
2 ID2 Cost 1 Cost 2
3 ID3
4 ID4 Cost 1 Cost 2
5 ID5
6 ID6
Would like to find a way for Excel to check cell B1 of the Master
spreadsheet and if blank or zero go to Subordinate 1 and pull in data from
cell B1. Then check cell C1 of the Master sheet and if blank or zero go
Subordinate 1 and pull data from cell C1. This process would repeat for all
rows in the master spreadsheet. Then Subordinate 2 would be opened and the
process repeated without overwriting data previously updated from Subordinate
1.
Any help, tips or advice would be greatly appreciated.
Eric