Excel help please (vlookup I think)

Django x2 · 4 Feb 2010 at 09:35

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

Django x2 · 4 Feb 2010 at 09:50

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?

Django x2 · 4 Feb 2010 at 11:06

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

Django x2 · 4 Feb 2010 at 11:47

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?

Django x2 · 4 Feb 2010 at 13:56

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.

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.

Django x2 · 5 Feb 2010 at 10:14

My brain hurts at the minute! I'll give it a crack this afternoon. Thanks again for your help so far. Much appreciated.

Django x2 · 8 Feb 2010 at 09:14

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).

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"

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

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.

Django x2 · 8 Feb 2010 at 11:57

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!!!!

Django x2 · 8 Feb 2010 at 12:00

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:

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!)

Django x2 · 8 Feb 2010 at 12:50

Pretty much hit the nail on the head.

Based on the other screenshots above, I should expect to see the returned results from sheet 1, row 2, to appear as:

Django x2 · 8 Feb 2010 at 13:06

ok, I think. But Sheet1, B14 is a blank cell. Do I have to do these list ranges more than once or something?

It seems to me that you think I'm going to know all the values that will appear in the DB and list them out. But I don't. Anything could get chucked in there, be completely random and my cells need to reflect what they see from the database rows.

Ohhhhh, baby jesus is even getting sad now

Django x2 · 8 Feb 2010 at 13:43

what column are you classing as 'part number'? A? (sheet1).

You are right though, everything will be unique (or should be). I just can't seem to figure this out and its making me angry!!!

Django x2 · 8 Feb 2010 at 15:31

Malakite said:
Any way you could email the sheet mate, at work so can't see the screenies? Would be easier for me to try and help.

I will email my addy to your trust.

Sent, thanks matey

Django x2 · 9 Feb 2010 at 09:06

Hi mate, thanks. I pinged you the email again last night.

Django x2 · 9 Feb 2010 at 10:13

If I understand you correctly, I just want the user to highlight row 2, 3, 4, whatever, and it it makes the cells in the other worksheets change value based on the row picked.

Is that possible?