VLOOKUP makes me want to smoke crack...

  • Thread starter Thread starter ****cameron****
  • Start date Start date
C

****cameron****

Hi All,
I have a little problem that is causing me to;
1. swear loudly at my computer
2. swear loudly at myself for bothering to swear at a computer.

I constructed a workbook where i had a data entry screen for entering
of raw data. It was set up where by the heading row was dates and the
column row was sales person followed by sub heading of different
products etc. This was then sorted into a series of lookup tables
according to product ie: product one has a table with each sales person
for column headings and date along row headings etc. This worksheet
was used for an analysis page where i constructed a vlookup table in
which the user entered the dates needed (up to five) which was then
totalled and other statistics drawn off that.
my prob is this, someone with no excel experience changed the dates in
the data entry worksheet and caused the vlookup functions to stop
working properly giving the #num! value or just 0. I changed the dates
back properly but the vlookup table still produces the same result. If
I have to re-do the spreadsheet i swear my mouse will become a mace and
i will take out my fellow co-workers, i dont want this to happen so can
anyone help me?
cheers in advance,
cam
 
Are you using false? vlookup(xx,a1:d4,3,false)


--
John
johnf 202 at hotmail dot com


| Hi All,
| I have a little problem that is causing me to;
| 1. swear loudly at my computer
| 2. swear loudly at myself for bothering to swear at a computer.
|
| I constructed a workbook where i had a data entry screen for entering
| of raw data. It was set up where by the heading row was dates and the
| column row was sales person followed by sub heading of different
| products etc. This was then sorted into a series of lookup tables
| according to product ie: product one has a table with each sales person
| for column headings and date along row headings etc. This worksheet
| was used for an analysis page where i constructed a vlookup table in
| which the user entered the dates needed (up to five) which was then
| totalled and other statistics drawn off that.
| my prob is this, someone with no excel experience changed the dates in
| the data entry worksheet and caused the vlookup functions to stop
| working properly giving the #num! value or just 0. I changed the dates
| back properly but the vlookup table still produces the same result. If
| I have to re-do the spreadsheet i swear my mouse will become a mace and
| i will take out my fellow co-workers, i dont want this to happen so can
| anyone help me?
| cheers in advance,
| cam
|
|
| ---
| Message posted
|
 
hiya John,
here is the formula i used;
=VLOOKUP(B10,'LOOKUP TABLES TO BE HIDDEN'!4:9,MATCH(ANALYSIS!D3,'LOOKUP
TABLES TO BE HIDDEN'!B3:IV3,0)+1,FALSE)
i have just changed cell references for each value and checked those
and they seem to be correct.
 
Back
Top