Vertical Look Up

[GayleZ]

I have a rather large (to me anyway) log sheet that I track my work on (medical transcriptionist) because I get paid by production. I have figured out everything I want it to do except for 3 items which I believe would all be the same function.

I have a workbook that has 3 worksheets in it, each a 2 column table:
Physicians: Dictator ID and Dictator Name
Work Types: Work type ID and Work Type Name
Patient Types: Patient type code and Patient type description

The above is what I believe is called my database file.

I have a separate workbook that has a page for every pay period of the year where I record many types of information about the jobs I do and notes.

I repeatedly will do the same type of job by the same dictator with the same patient type in one day. Time I could be spending on production but I still need to document this information just in case.

I would love to enter "just the dictator's ID number" and have it look up the number in the "Physicians" sheet and then enter the physician's name adjacent to that number.

The same goes for the work type and patient type.

I believe Excel can do just that but I have a whole bunch of people telling me I should be working in Access and opening up a spreadsheet for my production/pay from there.

Am I correct that I can do the above without having to learn Access?

[TMS]

Sure you can do it in Excel if you don't want to use/learn Access. Access is obviously more "appropriate" for what is, essentially, a small relational database.

I'm guessing your spell checker is converting doctor into dictator ... but maybe not?

Have a look at the VLOOKUP function for your purposes.

Regards

[GayleZ]

Thank you. Now that I know I can do it, can you direct me somewhere or to someone who can help me write the formula I need? I have tried several help articles and I just can't seem to figure it out.

Basically, on my job log, I want to type in the dictator ID number into:
Workbook: Job Log, any pay period sheet, any row, column P,

When I hit enter I want the formula to do:
Go to Workbook: Hospital, Sheet: Physicians, look down column A for the number, read what is in column B next to the dictator ID number I entered, and either (a) copy and paste it into the same cell I entered the number or (b) copy and paste it into the cell in the column adjacent to it (back in the Job Log Workbook).

I realize this is a lot to ask (a person) to help me with and I am not asking because I don't want to learn Excel as I have learned a significant amount already, it is just that I am pressed for time (lack of time to read all the chapters up to Chapter 12). I have multiple formulas in my job log that do quite a bit of calculating for me or do true/false things. I am only lacking the how to on this particular formula that would save me oodles of time.

Thanks greatly.

[TMS]

The formula you need, in most cases, is a VLOOKUP. This takes the form:

=VLOOKUP(Search item, search range, column to return (within the range), FALSE)

The FALSE is for an exact match.

So, for example, if you have a sheet called Work Type with the ID in column 1 and the description in column 2, you would put something like:

=VLOOKUP(A2, 'Work Type'!A:B,2,FALSE) to get the description linked to the ID.

Or, another way is simply to use Data Validation with Dynamic Named Ranges to select the various entries for each "event".

Why not mock up a sample workbook with some representative "tables" and post it in the thread. We can then put some suggestions into practice for you.

Regards

[GayleZ]

Thank you tons. Here are the 2 workbooks I want to use. I have highlighted the columns that I want to enter data in in pink on the Job Log.

[TMS]

First question: do you want/need to keep the Work Type and Physicians Tables separate from the job log?

My inclination would be to have those tables in the same workbook. Not a huge problem but you will be asked if you want to update links every time you open the workbook.

And, in Access, the just be separate tables within the same database file.

Regards

[GayleZ]

They could be in the same workbook. That would be awesome actually.

[TMS]

OK, try this and let me know if it helps.

The sheets are all protected but with no password. You can only select cells which are unlocked; that is, the cells where you need to input data. The cells with formulae are locked so you cannot, as it is set up, select them. This is purely to avoid potential corruption.

I've copied across the Physicians sheet and the Work Types sheet. I have also set up a "Reference" sheet for other Data Validation lists. You could also have DV for things like "yes", "no", N/A", etc

I have set up Dynamic Named Ranges for anything used in Data Validation or Lookup so you should be able to just add entries to any of the lists without having to change anything.

And, lastly (I think), I have modified most of the formulae so that nothing is displayed where the row has not had data input.

Enjoy, let me know how you get on or if you have any questions.

Regards, TMS

[GayleZ]

OMG!!!! You are absolutely awesome!!!! This is incredible to say the least. I don't know how I thank you enough!!! I can't wait to go on our trip (where there is no internet) over New Year's. I am going to take my Excel Book with and read it from cover to cover. Maybe I can help someone someday like you did for me. Once again, thank you, thank you, thank you!

[TMS]

I take it that it does what you want, give or take?

Cautionary note: It has not been "destruction tested" so please make sure that the calculations are what you would expect, especially in relation to all the rolling totals.

And I wasn't quite sure about the way you have earned today and total earned and how you differentiate and/or break the run. At the moment, you'll just have two columns with the same values.

Anyway, play with it, break it, and let me know if anything needs changing ... and I'll see what I can do.

Regards, TMS

[GayleZ]

Once again, what you did for me is absolutely incredible and greatly appreciated. I thought you might want to see the finished product. Not quite sure what I did on lines on the 2 lines below the titles (rows 3 and 4) but I managed to make it all work. You have given me a wonderful gift that will benefit me tremendously.

Thank you again,
Gayle

[TMS]

You're welcome ... and the final product? Where's that?

Regards, TMS

[GayleZ]

Apparently my workbook was too big, so I took February through December out. Here it is.

[TMS]

I see you've done some work on it


Glad to have helped. Easier than learning Access, though?


Regards, TMS

[GayleZ]

Hi again, I loved your rolling eyes emoticon because I am sure your going to be doing that and then some LOL. I ended up adding some more checkpoints for myself after I worked with the sheet for a pay period to set it up for the coming year to include all I did during my shift. However ..... On my final set up I lost the connection to the physicians. I am confident I have all I need on it and I PROMISE NOT TO ADD ANY MORE LINES! I swear I will only make copies of the sheet!!

Can you please please re-establish the link for me. I have learned a lot from what you have helped me with already and you have been so awesome.

Thank you! Thank you! Thank you! Ahead of time.
Gayle

[GayleZ]

Oops. Got so frustrated with myself I forgot to attach it.

Thanks again.

[TMS]

=PHYSICIANS!$A$2:INDEX(PHYSICIANS!$A:$A,COUNTA(PHYSICIANS!$A:$A))

=PHYSICIANS!$A$2:INDEX(PHYSICIANS!$B:$B,COUNTA(PHYSICIANS!$A:$A))

Easiest way to fix these is to copy an unbroken one, for example:

='Work Types'!$A$2:INDEX('Work Types'!$A:$A,COUNTA('Work Types'!$A:$A))

As yo can see, there's a bit of a theme.


Regards, TMS

[GayleZ]

I am a master at copy and paste LOL. I got lost somewhere in between the paste and your original code which was:

=IFERROR(VLOOKUP($04,DictatorTable,2,FALSE),"")

I was able to recover the function partially on the rows that already had data in them (the Dictator ID#) by doing copying and pasting the above and changing 'DictatorTable' to the sheet name:

=IFERROR(VLOOKUP($04,PHYSICIANS!A1:B578,,2,FALSE),"")

But then when I copied and pasted that into the cells below, I got:

=IFERROR(VLOOKUP($04,PHYSICIANS!A2:B579,,2,FALSE),"")

=IFERROR(VLOOKUP($04,PHYSICIANS!A3:B580,,2,FALSE),"")

I could not figure out how to keep the data range from increasing in rows. Also, on rows that had not been filled in yet, when I tried to fill in a doctor's ID# I got the following error:

The value you entered is not valid. A user has restricted values that can be entered into this cell. Retry Cancel Help

Does this tell you whether or not I am following the theme. I tried the code you mentioned just above but it gave me a #REF! error.

Thanks bunches.

[GayleZ]

Following your theme, I just did:

=IFERROR(VLOOKUP($O10,PHYSICIANS!$A:$B,2,FALSE),"")

and it pulls up the correct physician for the data that is already entered in the adjacent column; however, it still will not allow me to type directly into the Dictator ID column without receiving: "The value you entered ..." error.

Am I following you?

[TMS]

The "formulae" that I posted are for the dynamic named ranges ... the ones that were corrupted.

You need to look on the Formula Ribbon and click on Name Manager.

Then edit the entries for DictatorID and DictatorTable to replace the #REF! error at the beginning.

All you actually need to do is replace the #REF! with $A$2

Regards, TMS