macros from sheet to sheet

[Guest]

I am a little new at excel, and have looked for the answer to this a bit, but
I have not been able to find anything.

The spreadsheet has two "sheets" -- the second sheet is a list of names and
their corresponding client number. The first sheet is to record time spent
on each client. There was originally a macro setup so that when someone
wrote in the client name in Column B Sheet 1, the number (from sheet 2) for
that client would automatically come up in Column A Sheet 1. I did not
design the sheet (obviously!!) but I can't seem to make this work now.
Help!

Thanks!!!

 

[Guest]

Hi,

you don't need macro just enter this formula in the cell A1 sheet1:

=VLOOKUP(B1,sheet2!$A$1:$B$100,2,false)
assuming you data in sheet 2 is in cells A1:B100 and also names are in the
first column.
you can copy drag down the formula to where ever you want. so you can write
the name in column B ( should be exactlt like the name in sheet 2) and see
the appropriate number in column A.

Thanks,

 

[Guest]

Thanks!
That really helped. Is there a way to Lock that formula so that it can't be
erased? (If someone puts in the wrong client name, then backspaces in that
column, the whole formula erases.) I know it can be copied and pasted again,
but if I could lock it in there that would be ideal--not everyone using these
sheets understands the copy/paste idea.

Also, where can I look to find out what all of those items mean so that I
can learn how to devise that formula on my own?

Thanks so much,
eedmonds

 

[Gord Dibben]

By default, all cells are protected when sheet protection is enabled.

Hit CTRL + a(twice in 2003) to select all cells.

Format>Cells>Protection. Uncheck "locked" and OK out.

Select the cells you want protected and Format>Cells>Protection

Check "locked".

Tools>Protection>Protect Sheet. Note the options when protecting.

Supply a password and OK.

Excel's internal security is weak but this will prevent overwriting by accident.

If you want a workbook that lists all the Excel worksheet functions, download
this one from Peter Noneley.

http://www.xlfdic.com/

Very good examples and usage of all the Excel Functions.

Or just look in help for functions and examples.


Gord Dibben MS Excel MVP

 

[Guest]

Hi,

Thank you for your feedback, for lucking cells first you have unluck the
cells that you want to change (in your example it is column B becaus you want
to enter names) by selecting column B and then right click on the selected
cells and Format cells> protection and then uncheck the block checkbox and
then go to menu: Tools> protection> protect sheets ( you can put a password
or leave it empty ) and then click OK

Thanks,