PING - Excel gurus

[Suburban Rhythm]

I've probably done this 100 times, and drawing a blank

How do I insert a 'check box' into a cell?

[Suburban Rhythm]

Solved....View-Toolbars-Forms.....it's Monday

[DaddyTorgo]

BUMPITY!

question for excel gurus!

I have 2 spreadsheets. one has a list of values (6 digit numbers). the second has the same 6 digit numbers as well as a country name linked to the numbers. i want to move the country names into the first spreadsheet (which also has additional data).

Someone advised me to use a vlookup to do so. Thought maybe one of our resident excel gurus could guide me through how i setup the formula to do that - I've actually never used one before.

[Passacaglia]

Assuming the values are in Column A of both tabs, and the country name is in Column B:

vlookup(A1,'[File name]Tab name'!$A$1:$B$10000,2) -- this would go in the column where you want the country names to go.

Keep in mind, the spreadsheets are now linked, so you'll need to recognize that if you move files around.

[DaddyTorgo]

Originally Posted by Passacaglia

Assuming the values are in Column A of both tabs, and the country name is in Column B:

vlookup(A1,'[File name]Tab name'!$A$1:$B$10000,2) -- this would go in the column where you want the country names to go.

Keep in mind, the spreadsheets are now linked, so you'll need to recognize that if you move files around.

awesome. You just might be a lifesaver Pass!

[finkenst]

Originally Posted by Passacaglia

Assuming the values are in Column A of both tabs, and the country name is in Column B:

vlookup(A1,'[File name]Tab name'!$A$1:$B$10000,2) -- this would go in the column where you want the country names to go.

Keep in mind, the spreadsheets are now linked, so you'll need to recognize that if you move files around.

or just the copy data into another worksheet/workbook and paste as values and not formulae.

[Passacaglia]

Originally Posted by finkenst

or just the copy data into another worksheet/workbook and paste as values and not formulae.

Right -- he loses some functionality if he does that, but it's certainly an option.

[DaddyTorgo]

that's probably what i'll do (the copying). I don't need the functionality, just the raw values

[DaddyTorgo]

thanks guys. I will give it a go tomorrow mid-morning, might end up having to come back here whining about getting it to work though! lol

[DaddyTorgo]

bah - can't get it to lookup right

[DaddyTorgo]

both values i'm looking up by are in column B. the column with the data i want to import is column F. and to make it easy i put both of the spreadsheets together into the same worksheet so all i had to do was put in the tab name.

I copied Pass's formula exactly, substituting B's for A's and the F for the B and somehow I ended up with a value from column C brought into the spreadsheet.

=VLOOKUP(B2,LISTE!$B$1:$F$10000,2)

how's it getting a value from column c in there??

edit - no matter what it's pulling values from column c - weird. maybe i can try putting the countries in column c and see if that works

[Passacaglia]

It's because of the 2. That's specifying which column in your range (which starts with B, and ends with F, per your formula).

[Passacaglia]

So B=1, C=2, D=3, E=4, F=5. Change that 2 to a 5.

[DaddyTorgo]

okay wait i'm confused - changing the 2 to a 5 will fix it? will try that right now

[DaddyTorgo]

okay that seems to work for some, but it's still not getting all of them right - like it has one company that i can do a find and clearly see is in japan, but it's pulling up US...

[Passacaglia]

I'm not sure about that. My best guess is to add a 0 after the 5.

=VLOOKUP(B2,LISTE!$B$1:$F$10000,5,0)

[Passacaglia]

That changes the function from finding an approximate match to an exact match -- are your 6-digit numbers pretty similar?

[DaddyTorgo]

ohhh i do believe that seems to have helped

[DaddyTorgo]

they are very similar - exact matching would be what i need - they are unique 6 digit ID codes

[DaddyTorgo]

Pass - I owe you one man. Next time I'm in your neck of the woods or something.

[DaddyTorgo]

that absolutely did it

[Passacaglia]

Awesome!

[Suburban Rhythm]

OK, another one. And this seems a little more complicated than the poor one i started this thread with.

I've got a spreadsheet that contains data across columns A - W. All cells have a value-- EXCEPT certain cells in column F.

Trying to create a macro that makes the data all pretty for the client. I am good with all the formatting, except inserting rows to break apart the data.

I currently just have the macro choosing the lines that need the inserted row (about 500 lines). However, as soon as I have a row added/deleted from the data (running in another application and exported to Excel), my rows will be jacked up.

Is there a way I can the macro "look" to the empty cells in column F, and know that is where I want the blank rows inserted?

Thanks

[gottimd]

Do you mean an ISBLANK statment?

=IF(ISBLANK(A1),"Y","")

[Suburban Rhythm]

Sort of...except I do not want to populate the cell if it is blank.

For example all cells are filled with data A1: F20. Except for C8 and C15.
So I want to insert a row above row 8 and row 15.

Would I be able to use an ISBLANK statement? Can I use =IF(ISBLANK(C1:C500),"XXXXXXXX", "")

With the XXXXXXXX representing the row insert function?

Thanks