Where to start with a formula

  • Thread starter Thread starter LeslieJ
  • Start date Start date
L

LeslieJ

Hi ... this forum is amazing! If anyone can help out I'd reall
appreciate it. I've searched through tips & forums, but I'm not eve
sure about the terminology of what function I'm looking for an
apologize for repeating an earlier inquiry ...

I'm creating a spreadsheet--call it Sheet 1--with (to simplify) a th
client's manager in Column A, client account ID in Column B, and fe
billed to client in Column C.

The fee amount in Column C changes quarterly so I'd like to be able t
create some kind of formula that will look at cells in anothe
spreadsheet--Sheet 2--with the current fee and put it into Column C o
Sheet 1 referencing the client account ID in Column B--so the correc
fee matches the client account.

Example ... Sheet 1 (Master Info), Cell B2, Client account is 1234 an
fee (Cell C2) is 25. On Sheet 2 (Updated Info), Cell B2, Clien
account is 1234 and fee (Cell C2) is 30. I'd like to have the fee i
Sheet 2 updating Sheet 1.

The reason for this is there's over 850 accounts, and it's unbelievabl
time consuming to do this manually.

If anyone has a suggestion on what formula to use and how to write it
that would be fantastic. My Excel knowledge is all self-taught & m
employer thinks I should 'just know this'. Thanks,

Lesli
 
Leslie,

Something like

=VLOOKUP(B2, Sheet2!B2:C100,2,FALSE)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ok this could get very confusing, but I'll try my best. I
am going to assume that you are working in one worksheet
and just using different tabs. In column C (sheet 1) use
this formula: =IF(COUNTIF('Sheet 2'!B:B, B1), VLOOKUP
(B1,'Sheet 2'!B:Z,2,0), "")
What its doing is comparing the cell B1 in Sheet 1 to all
of column B in Sheet 2. If it finds an exact match it
will populate the cell with your chosen cell. The chosen
cell is determine by the last part of the equation. You
have to set it up in table format with the table starting
with your search field ( I have specified your table in
sheet 2 as going from B out to Z (B:Z in formula) and to
populate sheet 1 with the matching cell in column 2 of the
table. Since your table begins in column B it will
populate the cell in sheet one with the info found in
column c of sheet 2. (example B:Z,3,0 would fill in the
info for column d; B:Z,4,0 with column e; and so forth)
I'm sorry if this is very confusing, but it works wonders
for very large spreadsheets where you are comparing or
populating cells from other tabs. You can just fill the
equation down the page and it will change your reference
cell to B2, B3, ...automatically, but it will still search
the same fields in sheet 2.

Hope this helps,
Reid
 
Back
Top