broro183 Wrote:
Hi,
re duplication of the vlookup formula:
DaddyLongLegs' suggestion of using dollar signs seems to be what you
need. Using dollar signs makes a reference "absolute" rather than
"relative" ie it doesn't change - check out Excel Help for more detail
[F1].
To overcome the "#N/A" problem after widening column H and half the
number of times a vlookup is performed since you have more spreadsheets
to copy your formula into I would change DaddyLongLegs sugestion of (as
per your layout):
=IF(ISNA(VLOOKUP(G6,$D$6:$E$100,2,FALSE)),0,VLOOKU P(G6,$D$6:$E$100,2,FALSE))
from above by inserting another column before column H entering
=VLOOKUP(G6,$D$6:$E$100,2,FALSE)
into the new column H (this can column can be hidden later). Then enter
=IF(ISNA(H6),0,H6)
into the "Pts" column (ie the old column H, now column I).
"There has to be a shortcut for this..."
Yes, there is, repeat the column insertion to the left of each set of
lookups.
With the references for the lookup range now being "locked" to columns
D & E, you should be able to select the 2 cells H6 & I6, copy them,
paste them down the rows needed & the same in the new columns across
the page.
Now the new columns can be hidden.
hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
Thanks for the response. This has helped but has lead to other
questions..lol.
This formula that you gave me:
=IF(ISNA(VLOOKUP(G6,$D$6:$E$100,2,FALSE)),0,VLOOKU
P(G6,$D$6:$E$100,2,FALSE)) Where do I insert this? based on my
spreadsheet?? I have added the new column H, but i find when using the
above formula it works in some cells but not in others. I can change
the first and last G cell to match where i want it to pull but i don't
always get the points value that is assigned to the referencing pts.
column. I will get ZERO regardless of who i put in the field. Any
suggestions...It seems to work on its own, but not in every
cell....very strange...
--
AlienBeans
------------------------------------------------------------------------
AlienBeans's Profile:
http://www.excelforum.com/member.php...o&userid=32352
View this thread:
http://www.excelforum.com/showthread...hreadid=543500