Couple of quick questions - VLOOKUP maybe?

**Bakks** · 06-03-2008, 09:25 PM

I have a sales data worksheet with lets say 3 columns. The first column is the sales persons region in numerical format, second is their name and third (and onwards) is the data.

I then have an output sheet, in which I want to be able to setup to show salespersons name, and relevant data, based on an input of their region in say cell A1, i.e. so when 1 is input in cell A1 it will return all the salespersons names and data in this region, when 2 is input it shows all the salespersons names and data in this region.

Now an autofilter will work, but its messy. I want to use a VLOOKUP (=IF(VLOOKUP($A$1,Data,1,FALSE)=$A$1,VLOOKUP($A$1,Data,3,FALSE),""))but it will only return the first salesperson in each region, so at the moment it looks like the following
A1 = 1
Name 1 $XXX
Name 1 $XXX
Name 1 $XXX

I want it to appear as
Name 1 $XXX
Name 2 $XXX
Name 3 $XXX

How do I get around this?

While I’m here asking questions, I may as well throw another one at you guys.

Say I have a data sheet for each month, in columns I have the salespersons name, and in rows I have relevant sales data. I want to consolidate these sheets, into one, so in the columns I will have salespersons name – relevant data, and in the rows the month, so all data can be on one sheet. Is there any way to do this?

**Bakks** · 06-03-2008, 09:33 PM

Heres a basic sample, I'm also getting #N/A in row B now, I'm not sure why!!!! Thanks.

**ratcat** · 06-03-2008, 09:38 PM

Originally Posted by Bakks

......................

I want it to appear as
Name 1 $XXX
Name 2 $XXX
Name 3 $XXX

How do I get around this?

Sound like you need a sumif formula

Originally Posted by Bakks

......................
While I’m here asking questions, I may as well throw another one at you guys.

Say I have a data sheet for each month, in columns I have the salespersons name, and in rows I have relevant sales data. I want to consolidate these sheets, into one, so in the columns I will have salespersons name – relevant data, and in the rows the month, so all data can be on one sheet. Is there any way to do this?

sumif formula

Post a small example if further hwlp is needed

Cheers

**Bakks** · 06-03-2008, 09:43 PM

I don't want to sum any data though.... Small example attached above.

**ratcat** · 06-03-2008, 10:12 PM

Hi,

You need to correct the vlookup formula to work

Go to the "Output" sheet and copy the code int0 cell G3

Please Login or Register  to view this content.

Then copy drag down

Hope that helps

**Bakks** · 06-03-2008, 10:28 PM

Cheers, still doesn't help solve my issue of how to stop the recurring data when using the VLOOKUP in column A of the output sheet! Any ideas?

**Portuga** · 06-03-2008, 10:42 PM

Have you considered doing a pivot table from your data?
It will give you a lot of flexibility of analysis. Good thing is that once you insert more data, it will be updated automatically once you refresh the pivot.

You can also add those month columns to the pivot, having the monthly info as you want.

I inserted a update button.

See attached

**Bakks** · 06-03-2008, 10:45 PM

Well that seems to work doesn't it! I'm not all that familiar with Pivot Tables but will have to look into it I think. Appreciate your help

**Portuga** · 06-03-2008, 10:50 PM

With pivot tables, you actually wont need a tab for each month. You can have all the data in just one tab, working as a minidatabase. There you can use the month formula =month(a1) for example and add that column in the pivot also.