Data From One Worksheet to another

[magicdawg29]

ok, i have excell 2007.....
I will try to explain this the best that i can.....

On My first worksheet (titled 'Price List'), I need a formula that can look onto one of my other worksheet ( titled '8ED'), Match names Coloum A and then get the price from another coloum and return it to my price list.

the only bad thing is, is that in the worksheets other then 'Price List' the first 2 coloums are hidden.

If someone needs screen shots i can post them, just let me know.

If there are any questions or suggestions please reply to this post, i NEED to figure this out soon, so if there are any other means of communications, that will work with me also.

Thanks
Kory

[Paul]

Even if the rows are hidden, a vlookup function will pull the proper data.

For example:

=VLOOKUP(A1,Sheet2!$A$1:$E$20,2,FALSE)

This will try to match the value in Sheet1!A1 with your list in Sheet2 column A, and then return the second column in your array (A1:E20 is an example, change yours to suit).

[magicdawg29]

Even if the rows are hidden, a vlookup function will pull the proper data.

For example:

=VLOOKUP(A1,Sheet2!$A$1:$E$20,2,FALSE)

This will try to match the value in Sheet1!A1 with your list in Sheet2 column A, and then return the second column in your array (A1:E20 is an example, change yours to suit).

Ok, here in your example you even state that it will look at sheet2 column A, i would need it to look at column C and then return data in column D

I did try to attach My file to this but it is tooooooo big

I know that i am not explaining myself very well...... and that makes it hard to people to be able to help. I really wish i knew of a better way to show or explain.

Kory

[Paul]

All you have to do is change the array of data in that case:

=VLOOKUP(A1,Sheet2!$C$1:$D$20,2,FALSE)

That will lookup the value of sheet1!A1 in the range C1:D20 on sheet2 and return the value from column D where Sheet1!A1 matches Sheet2 column C.

Change "sheet2" to the name of your second sheet.. i think you mentioned 8ED? Also, if your data goes beyond row 20, simply change that number, for example:

=VLOOKUP(A1,8ED!$C$1:$D$1899,2,FALSE)

The $C$1 assumes your data starts in cell C1. If it starts in cell C2, change it accordingly. It's very versatile.

[magicdawg29]

OK, here is a very small example of what i am working with......

Sheet1 is 'Price List'
Sheet2 is '7ED'

Some where in Price list i want it to look at column A, Row 2, and it should see 'Abyssal Horror [7E]' NOW i want it to look at sheet2 '7ED' and find the words "Abyssal Horror" in column C, then i want it to look at column D, and then return that price to Column D, Row 2 of 'Price List'

that way i should be able to just Click and drag the box so that it will drag the formula down the column.

Now also i will want to do the same for column E in price list, but when it does the search or what ever it is called that it adds the word Foil to the name when it is looking on the sheet '7ED' so that it finds the correct one and then returns the correct value.


I hope that this helps everyone and we can get this figured out.

Thanks in advance for the help,
Kory

[magicdawg29]

Also, when i copy and paste that formula, i get an error? i have no clue why.

Kory

[Paul]

I don't know if it's because you use Excel 2007, or if you meant it to be this way, but on your 7ED sheet there are two spaces before every title in column A. For me to read your sheet in Excel 2003 it had to be converted.

Based on the chance you actually have two spaces before each title on sheet 7ED, use these formulas for columns D and E on sheet PriceList:

In D2 put the formula:

Please Login or Register  to view this content.

In E2 put the formula:

Please Login or Register  to view this content.

If you don't have two spaces in your version of the file, remove the quotation marks with two spaces in between them, just after the VLOOKUP( part.

Make sense?

[magicdawg29]

ok, like i mentioned before, i am still getting an error when i put in this formula, i dont know if it is the fact that this is due to the fact that i am using excel 2007 or not? But these formulas give me an error and i dont understand why.

any more help would be great.

Kory

[Paul]

This is a standard Excel VLOOKUP formula, nothing special about it. It should not work differently in Excel 97, 2000, XP, 2003 or 2007.

The formulas I provided work exactly as you have requested on my computer, so either your request is inaccurate, or your data is setup differently than the example sheet you provided.

What error are you getting? If you get an "#N/A" error, it might simply be that the data you're looking for in cell A2 on the Price List sheet is not listed in 7ED column C. In the example you provided, there is very little data on sheet 7ED, so many of the records in sheet PriceList return "#N/A". Also make sure that your list on sheet 7ED is sorted ascending (a->z or 0->9). VLOOKUP requires that the lookup area be sorted.

If you still cannot get it to work, then either zip and post your actual workbook, or someone with Excel 2007 may need to work with you (even though it shouldn't make a difference in this case).

[magicdawg29]

OK, here are some screen shoots of the error that it returns.

And those are of the Example spreadsheet that i posted. I am not even trying it on the real one, just cause i dont want it to mess things up.

[magicdawg29]

Here are the screen shoots......

Screenshoots were croped, due to size restrictions.

Thanks for your help,
Kory

[Paul]

In an open column in row 2 on sheet Price List (let's say F2), put the formula:

Please Login or Register  to view this content.

Does that return the title in cell A2 excluding the [7E] at the end? If so, in G2 put the formula:

Please Login or Register  to view this content.

The only way to make a vlookup formula more simple is to have the search and lookup data on the same sheet. If you still get an error in G2 I don't know what to say.

I just received my copy of Office 2007 in the mail today, coincidentally, and I'll probably be installing it within the next few days/week. If someone else can't figure it out by then, I'll test this same setup on that installation.

[magicdawg29]

Even with that formula placed where i was suposed to...... i still get that same error.

Kory

[magicdawg29]

OK, OK, OK, i got it figured out........

i use some strange international excel CSV file and i have my seperator set diffrently......

SO all i had to do was take and change all the commas to a semicolun and it works

WORKS GREAT, THANKS

Kory

[Paul]

Whew, glad you got it working. I was beside myself on why it wouldn't work.

Have a good weekend!