I have two tab delimited spreadsheet files of a product database. My
MAIN concern here is that I need to import the prices from one
spreadsheet, to a specific column in my original spreadsheet, and I
need those prices to correspond with the respective product IDs. Is
this possible ??? thanks in advance guys!

It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.

Dave O wrote:
It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.


Ok... My main spreadsheet (tab delimited file) I have it setup as
follows:

A B C
D

ProductID49483 Brief Description Image.jpg 3.00
ProductID93094 Brief Description Image.jpg 5.00
ProductID30940 Brief Description Image.jpg 8.00

my alternate spreadsheet (tab delimited file) is as follows:

A B

ProductID49483 4.50
ProductID93094 8.50
ProductID30940 9.50


I want to make it so that I can Import my alternate spreadsheet into my
main spreadsheet, but I only want it to alter column D on my main
spreadsheet (changing prices). I need a macro, or a formula which will
import column B from my alternate spreadsheet into column D in my main
spreadsheet. I also need this macro/formula to be able to correspond
the price with the correct productID...so it changes the correct
product to its designated price.

Any Ideas guys???

Have you tried using a VLOOKUP (using the fx button under the catagory =
Lookup & Reference)? Make sure you are able to sort in ascending order by
the Product ID on both tabs. Then using column A from the Main spreadsheet
as the Lookup Value, then on the alternate tab/spreadsheet use the columns
A:B for the table array, the col_index_num will be "2" which will be
referencing column B ( the prices), and you should set the Range_lookup to
false because you want it to utilize exact matches on the Product IDs. Your
formula would look something similar to this
=VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!!
"ChrisB" wrote:


Dave O wrote:
It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.


Ok... My main spreadsheet (tab delimited file) I have it setup as
follows:

A B C
D

ProductID49483 Brief Description Image.jpg 3.00
ProductID93094 Brief Description Image.jpg 5.00
ProductID30940 Brief Description Image.jpg 8.00

my alternate spreadsheet (tab delimited file) is as follows:

A B

ProductID49483 4.50
ProductID93094 8.50
ProductID30940 9.50


I want to make it so that I can Import my alternate spreadsheet into my
main spreadsheet, but I only want it to alter column D on my main
spreadsheet (changing prices). I need a macro, or a formula which will
import column B from my alternate spreadsheet into column D in my main
spreadsheet. I also need this macro/formula to be able to correspond
the price with the correct productID...so it changes the correct
product to its designated price.

Any Ideas guys???

I received an #N/A error. I think this might be on the right track to
helping me....can you elaborate a bit more? I am referencing the main
spreadsheet as alternate, and using A:G for the table array because A
is what i need it to reference in the main spreadsheet and G is the
prices on the alternate, using col_index_num 7 as that is the column
number, all with your formula and im receiving that error.



Smilingout_loud wrote:
Have you tried using a VLOOKUP (using the fx button under the catagory =
Lookup & Reference)? Make sure you are able to sort in ascending order by
the Product ID on both tabs. Then using column A from the Main spreadsheet
as the Lookup Value, then on the alternate tab/spreadsheet use the columns
A:B for the table array, the col_index_num will be "2" which will be
referencing column B ( the prices), and you should set the Range_lookup to
false because you want it to utilize exact matches on the Product IDs. Your
formula would look something similar to this
=VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!!
"ChrisB" wrote:


Dave O wrote:
It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.


Ok... My main spreadsheet (tab delimited file) I have it setup as
follows:

A B C
D

ProductID49483 Brief Description Image.jpg 3.00
ProductID93094 Brief Description Image.jpg 5.00
ProductID30940 Brief Description Image.jpg 8.00

my alternate spreadsheet (tab delimited file) is as follows:

A B

ProductID49483 4.50
ProductID93094 8.50
ProductID30940 9.50


I want to make it so that I can Import my alternate spreadsheet into my
main spreadsheet, but I only want it to alter column D on my main
spreadsheet (changing prices). I need a macro, or a formula which will
import column B from my alternate spreadsheet into column D in my main
spreadsheet. I also need this macro/formula to be able to correspond
the price with the correct productID...so it changes the correct
product to its designated price.

Any Ideas guys???

Hello,

If you haven't already got a solution :) sorry for the late response - got
tied up with work!

If you want your prices in the main spreadsheet to be updated with the
prices from the alternate spreadsheet then you will need to input the formula
on the main spreadsheet because this is where the update should happen and
reference the alternate spreadsheet as the table array because this is where
the updated prices will come from....does that make sense? Where you input
the formula is where the updated prices will be returned..so you want to put
your formula on the tab that needs the new prices and reference the tab that
has the info you need as the table array....

1. Make sure that your product IDs on both tabs are in column A (the
leftmost column)
2. Make sure both tabs have been sorted in ascending order by column A or
the product ID - if your info isn't sort in ascending order then the formula
will not work
3. on the tab that you want the prices to be updated (Main tab) insert a
new, blank column next to the column with the prices. This is so you will
not override your old prices until you are sure the formula works.
4. In the new blank column at the first row where you need an update price -
input your VLookUp formula = VLOOKUP(
5. Now, since you are on the tab that needs the updated prices you are
going to use the cell that contains the first product ID as the LookUp Value
in the formula..this will be in column A because you put your product IDs in
column A and that is how you are connecting these to spreadsheets by product
ID...typically it is A2 because your headings are in row 1...your formula so
far would be =VLOOKUP(A2,
6. Next you would input the table array into your formula this should
reference the tab that has your updates prices. This is telling Excel where
to go to find the new information to update the first tab....it should be
your alternate tab and referencing all of the columns from A to where your
new prices are located (A:B or A:G).....
7. Depending on which column the new prices are located in that column #
will be your col_index_num..
8. Range_lookup will be false

The returned values in the column you inserted should be your updated prices
for that product ID on your original/main tab. If that is the case and it is
correct (spot check 3 or 4 of them) then highlight this column and copy it.
Then using Paste Special - Values paste your new prices over that same column
(over the formula). You can then delete the column that contains the old
prices :)
"ChrisB" wrote:

I received an #N/A error. I think this might be on the right track to
helping me....can you elaborate a bit more? I am referencing the main
spreadsheet as alternate, and using A:G for the table array because A
is what i need it to reference in the main spreadsheet and G is the
prices on the alternate, using col_index_num 7 as that is the column
number, all with your formula and im receiving that error.



Smilingout_loud wrote:
Have you tried using a VLOOKUP (using the fx button under the catagory =
Lookup & Reference)? Make sure you are able to sort in ascending order by
the Product ID on both tabs. Then using column A from the Main spreadsheet
as the Lookup Value, then on the alternate tab/spreadsheet use the columns
A:B for the table array, the col_index_num will be "2" which will be
referencing column B ( the prices), and you should set the Range_lookup to
false because you want it to utilize exact matches on the Product IDs. Your
formula would look something similar to this
=VLOOKUP(A2,alternate!A:B,2,FALSE). Good Luck!!
"ChrisB" wrote:


Dave O wrote:
It's likely very possible, but we'd need to see some sample data so we
know what data points are available to work with. Please post sample
data.


Ok... My main spreadsheet (tab delimited file) I have it setup as
follows:

A B C
D

ProductID49483 Brief Description Image.jpg 3.00
ProductID93094 Brief Description Image.jpg 5.00
ProductID30940 Brief Description Image.jpg 8.00

my alternate spreadsheet (tab delimited file) is as follows:

A B

ProductID49483 4.50
ProductID93094 8.50
ProductID30940 9.50


I want to make it so that I can Import my alternate spreadsheet into my
main spreadsheet, but I only want it to alter column D on my main
spreadsheet (changing prices). I need a macro, or a formula which will
import column B from my alternate spreadsheet into column D in my main
spreadsheet. I also need this macro/formula to be able to correspond
the price with the correct productID...so it changes the correct
product to its designated price.

Any Ideas guys???