Excel help please (vlookup I think)

Django x2

Django x2

Django x2

Soldato
Joined
28 Sep 2008
Posts
14,207
Location
Britain
Gents,

I appreciate the recent Excel help and I need to cover off one more thing if that's ok. :)

I have a DB in access (2007) which I can export to Excel 2007 in a very simple click of the mouse.

What I need to do now, is, take the exported data and match the fields to another Excel document I already have.

So, Access has created a file called "test.xlsx" for instance, I need to run some form of start up macro which takes the info from "test.xlsx" and put it in to the fields I want in "myfile.xlsx" ideally with one click of the button!

Hope you can help :)
 
It sounds like you need to set up a query that refreshes when you open up the spreadsheet. If you did this then you don't need to create the file test.xlsx, you can pull the data directly from the database. I'm a little rusty, so maybe someone else can step in with a guide, but take a look at Data, From Access in Excel 2007 or Data, Import External Data, New Database Query from Excel 2003. It's then just a case of selecting the column headings you want to pull across.

Once you've sucked the data in then it's a case of Index and Match, or Vlookup to match the data you're after.

What do you want to do when you match the fields? Is it to populate the rows in Excel?
 
Ok cool, I kind of get this.

I've done the "From Access" conncetion within Excel 2007 and have got the table I want in the same workbook now (different sheet).

Basically, now, the data in the form (from access) has to be put into individial cells in the other worksheets. However, these cells need to change dependent on what row is selected from the database table.

Does that make sense?
 
Right, okay ... I need to figure out why you're doing this so I understand better. Are you trying to match entire rows in your excel sheet against the rows you've imported eg to check if there are discrepancies etc? Or are you trying to select an identifier in the first sheet that will lookup the identifier in the database sheet and pull all the data from the same row?
 
OK, I'll try and explain better.

This workbook (lets call it requestreport.xlsx) is made up of 4 or 5 worksheets. On each worksheet, a number of questions are asked with an empty cell to the right of each question.

These questions are also asked in the database (we'll call that request.accdb) and naturally the answers are shoved in a table.

Each column in the database table relates to a different question with each row being for a different product (for instance).

When I import the access db to Excel as part of the same workbook, I want the individual empty cells to be populated from columns of the database, but, dynamically change if the next row (product) is selected.

I hope that makes more sense :)

Thanks for all your help so far
 
pretty much yeah, but it needs to change dynamically.

For instance, there are 60 columns in the database sheet. These columns relate to the 60 blank cells spread throughout the other worksheets. Therefore, If I select row 1 from the DB sheet, it populates all the cells with that data, and when I click row 2 it populates the cells with all that data, etc, etc

Sorry, am I maxing something easy seem hard?
 
The difficulty comes in selecting the cell from the database. If you're talking about literally clicking on the cell then there's VB code to do with ActiveCell etc, but that's quite complex. It sounds like the best way would be to copy each of the row entries for the database into a seperate list and use this as the lookup for a dropdown list eg

A1:A20 = the list of 20 row headings - products I think you mentioned ...

Now set B1 to List using Data Validation and select A1:A20. What this does is enables a dropdown list f all the products when you click in B1.

Next, for each blank cell in the data entry sheet you need to create a vlookup that looks at the cell B1 and finds it in the databse eg

vlookup(B1,databasearray,2,false)

this finds the same value as picked in B1 in your database array (name the range would be easier than cell reference such as B2:T500). Once it's found an exact match then it moves across 2 columns (including the Product column) and returns the value.

For the next blank cell use the same formula but change the 2 to a 3, then 3 to a 4 etc.

Once you get this working we can change the 2,3,4 entries to a MATCH function so that it's more manageable. But we can look at this once you get a few of the blank cells working.

I'm out for a few hours, but I'll check back later.
 
Is there not a macro recorder in Excel 2007? If the database is always the same and the values you want are in the same place, then you should be able to record a macro.
 
fretted said:
Is there not a macro recorder in Excel 2007? If the database is always the same and the values you want are in the same place, then you should be able to record a macro.
Click to expand...

Won't work I don't think as each time the DB is imported the data will at least have additions (and maybe revisions)

Jed, thanks for all your help. Here is my formula but it doesn't seem to work. This could be a long post so I'll explain it all properly to you:

Code: 
=VLOOKUP(Sheet2!B1,Sheet1,2,FALSE)

Sheet 2 is my 'list' of all the database fields in the table (vertical). I then clicked in cell B1 and did a list data validation selecting all the rows from Sheet 1 (database import) A1:A60 (in my case).

Sheet 1 is the sheet that was imported from Access. So, A1, B1, C1, D1, etc contain all the field headings, then A2, B2, C2, D2, etc contain the results (for that product). A3, B3, C3, D3, etc contain the results for the next product, and so on.

Therefore, if somone selects Row 2 from the database sheet, (lets take row 2 for instance, and let's assume it's Adobe), it will populate the other sheets based on their cells.

So, I might have a sheet which is called "Product Details". This will have 6 empty cells in it, say, B4, B8, B10, D4, D8 and D10.

From the database sheet, A1, B1, C1, D1, E1 and F1 will be named as:

Software, Version, Date, SP, Owner, Location

Their results (row2) will be as follows:
Adobe, 9, Jan2010, 0, Me, London

These results I then need to get put into the 6 empty cells in the "product details" sheet, B14, B8, B10, D4, D8 and D10 respectively.

Phew, sorry for the giant post. I don't think I can explain it anymore than that. :)
 
Right what you need is a dynamically growing list, or simply type in the product manually.

First things first though, the vlookup second part is slightly wrong:

Sheet1 should be Sheet1!A1:Z10 ... for example. It needs to be the range of cells you want to look up against.

EDIT - unless Sheet1 is the named rasnge you've created.

I'm tied up a bit now ... my 17month year old son is screaming in my ear! I'll get back to you later :D
 
Just a quickie regarding the dynamic list of products. I can't think of a way of auto populating the list whenever the database query is refreshed without using VB. Maybe someone else here can look at that.

However, you can do it manually each time by selecting the first column in the database sheet and use auto filter - advanced. In 2003 it's Data / Filter / Advanced Filter. Select the column you want to look at, select the cell where you want to copy the list to and make sure you tick the box for unique records only. Actually, if you record a macro of you doing this then you can assign it to button and just press it every time you open the spreadsheet. It can also be assigned to run on workbook open automatically, but I'm a little rusty ...
 
Still doesn't work. I now have this as the vlookup

Code: 
=VLOOKUP(Sheet2!B1,Sheet1!A1:A100,2,FALSE)

I think some screenies might help (although I don't expect you to help any further, I would be grateful if you could :) )

So, here is one of 5 sheets (I've created). You will see the blank cells are numbered at the moment (just so I can map the fields with the database).

capturero.png


Here is how the data looks when I "get data from Access". I'm only testing this at the mo, hence only the one product in row2. This is called "sheet1"

capture1qn.png


And, after I paste the list and transpose it (to vertical), select the range and do the drop down.

capture2q.png


Hopefully you can see now what I'm trying to do. Basically, map each of my empty cells with the value of the name of the database which returns whatever value is in the database when a row (row2 in our case) is selected.
 
First things - the vlookup is only searching a 1 column array, but you're asking it to go across to the second column ie:

VLOOKUP(Sheet2!B1,Sheet1!A1:A100,2,FALSE)

Should be VLOOKUP(Sheet2!B1,Sheet1!A1:B100,2,FALSE), assuming you just want to go across 2 columns. In reality, it's more likely to be something like A1:Z1000 etc, for 26 columns ... or whatever you need.
 
Ok, hold on. In my second screenshot, I select all of Row1, copy it, then paste it (transposed) to the other sheet. I then select all that data (actually, 100 rows, A1 to A100) then do the data validation as a list in B1.

I guess I just can't see how this will work when data is added from the access database and appears in Row 3 (second screenshot).

I still get an #n/a error when I put my vlookup code into the empty cells (screenshot 1)

I can't honestly belive this would be this hard, I wish excel had cell mapping options (like cell IDs) which you could map to a range, easily!!!!
 
Ah, hold on, where it says 01_package_name, in B1, that now appears in the cell in my first screenshot (labelled in red 01).

But Its meant to display whats in row2, not the field name. This is depressing!!!

Edit: So it now looks like this:

capturehe.png


And my vloolups for that page look like this:

=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,0,FALSE)
=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,1,FALSE)
=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,2,FALSE)
=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,3,FALSE)
=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,4,FALSE)
=VLOOKUP(Sheet2!B1,Sheet1!A1:CV1000,5,FALSE)
etc, etc

But, why is it not actually showing the data from Row2, sheet1???

(I'm getting all excited now!)
 
Last edited:
Could you show us screenshots of what you would like to be your finished product? If I get this right, you want an excel front end to an Access database.
 
..and you are getting #N/A# because your vlookup is not finding '01_Packaging_Name' in col A of sheet1 (your Access database import)
 
You must log in or register to reply here.
Back
Top Bottom