Hi there, I\'m trying to make a sheet to keep track of the translation tasks I c

Question

Hi there,

I'm trying to make a sheet to keep track of the translation tasks I complete monthly, and as I have different rates for different services and clients, filling in all the data by hand could become time-consuming. I'd like to have as many of the details as possible filled in automatically, and I'm not sure how I'd go about it. Any help would be very much appreciated!

At the moment, the sheet I've come up with has columns for Client (A1), Rate (B1), Word Count (C1) and My Ref(D1). 'My Ref' will contain a client code and the number of the job, e.g. the fifth project I complete for Client ABC would look something like ABC-005.

Ideally, I would love it if I could have Excel pick out information from the 'My Ref.' cell and use to fill in other details. For example, if I could create a rule to make Cell A2 display the full name of the client using the code in D2 (My Ref.). This rule would look something like: 'If D2 contains text 'ABC', A2 should display text 'Abacus'. And/or, I could create a rule to make B2 (Rate) show a value dependent on the client code in D2, e.g. 'If D2 contains text 'ABC', B2 should display value

Explanation / Answer

You can achieve mosts of those using e.g. 2 sheets. 1st for your work tracking and the 2nd for clients.

The 1st you could use the same columns you're using now for the 2nd you could put something like client ref, value. Then using VLOOKUP you can fill each cell on the 1st sheet looking up on the 2nd. this function does pretty much all the work for you.

Assuming you use only 3 letter from client for you ref

Sheet 1 - name it "tracking"

[A]My ref -> ABC-001

[B]wordcount -> number

[C]client =VLOOKUP(LEFT(A2;3);clients!A:C;2;FALSE)

[D]rate =VLOOKUP(LEFT(A2;3);clients!A:C;3;FALSE)

[E]price =B2*D2

Sheet 2 - name it "clients"

[A]client ref ->ABC

[B]name ->Abacus

[C]rate ->

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.