Auto replace, can it easy be done?

M

mbogda

I have a series of tables where by I put in each tenant's name (column
a) and their lease end date (column b) and add any special comments or
irregularities in the leases in column c. Right now we either type in
each individual comment or use number codes, or essentially footnotes
where we define them somewhere else.

Is there any easy way to assign a number to a comment so that if you
enter 1 it gives "comment A", 2 "comment B" and so on and so forth. It
would be great if you could add multiple comments and not have the
formatting go all wonky but I'd be able to live with that if we could
get the first to work.

Does anyone have any suggestions?

Thanks,

Matt
 
M

Mark

Create a new table with 2 columns of information: 1st column should have the
comment code number, second column should have the comment. Let's assume
that you start entering the data in A100 and you have 7 codes/comments.

In cell D1 (or whatever first row of data is) enter:

=vlookup(C1,A100:B106,2,false)
 
M

mbogda

Great thanks ever so much for your help Mark, as soon as you mentione
the vlookup it all came back to me (well at least for that part). Th
one thing that still tends to bug me is that after I use the vlookup i
particular for a date I can't use the date for a pivot table.

For example if I were to have several columns with various dates (Ma
10, 1982; June 10, 1982; June 10, 1992) the pivot table will com
back with a separate value for each of these, even if I specify it t
display on the year it takes into account the day and month. Sometime
I get a set of speadsheets with various dates, it would be great to b
able to simply do a pivot table based on their data entry. Right now
have been forced to manually input the year for each corresponding dat
in an adjacent column and have used this manually input date. Is ther
an easy way to have excel only sort by the year? If I could get that t
work in conjunction with the Vlookup tool I’d be set and think I coul
save a ton of time.

Thanks again for your help 

Mat
 
S

SkiBumScot

Hi Matt,
Hopefully this can help with your pivot table question.

Creat youe Pivot with the Date in either the Row or Column area.

Right Click on the Grey "Date" Cell (not in the layout wizard but the
actual Pivot)

Then go to "Group - Show Detail" - "Group"

You should then be able to define the grouing by Day, Month, Quarter or
Year.

Went looking for this in response to your question, never used it
before but have realized it will save time in a few reports! Thanks for
the impetus.

Hope this works for you.

Phil
 
Top