INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Promblem with Access data imported into excel

Promblem with Access data imported into excel

(OP)
I connect to an access table from Excel. The data comes with filters set. I cannot remove the filter.
The presence of a filter is not significant to me, but I cannot use the data for a vlookup. Something seems to be wrong with the way the data is imported into Excel.

I have tried referring to the range of data several ways: (1) range; (2) named range; (3) inserting as a table.
In each instance, I get a #REF error in the vlookup cell.

If I manually input the data to test the vlookup function, my vlookup works.

What could be causing the #REF error?

Thank you.

2Rowdy

RE: Promblem with Access data imported into excel

Hi,

How are we to determine anything of relevance to your specific data without any knowledge of your specific data?

We could speculate, but, you know, speculating in public.....

Please upload your workbook.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

(OP)
Thank you Skip. I appologize for the ambiguous question.

I have tried various scenarios on my excel spreadsheet and I do not think it is an Access problem.
The problem is the implementation of the Vlookup formula. I do not know the solution. It has become clear to me that this is an excel question and not an access question. I must appologize for that as well.

In one instance, I am trying to use a Unique value for my lookup; in another I am trying to nest the vlookup within an "If" formula.

I thought the issue may be duplicates in the lookup table. However, I removed the duplicates in my "hybred table" with no change in results.


Formula 1: =VLOOKUP(A13,HybredReport,4,1)

** Formula 2: =IF(J12=1,VLOOKUP(A13,$I$12:$L$17,4,0)," ")

As you suggest, I have uploaded the spreadsheet for review and comment.

Thank you very much for your help!

Robert

RE: Promblem with Access data imported into excel

Okay I found it.

The issue is the HybredReport range.

You changed the reference from all 4 columns to only the last 2 columns, because you have combined a reference key to data in column A (the account numbers) and the data in row 6 (Prospect Names).

Hence, the lookup value is a concatenation of the two...

=VLOOKUP($A13&C$6,HybredReport,2,TRUE)


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

(OP)
Skip:

Please look at the sheet "If + Lookup"

In column C, there should be a number for accounts 6100.12, 6100.14 and 6200.11
Column C: The formula places data in accounts 6100.12, 6100.10 (which should be in column D) and 6100.13, which there is no match at all.

Column D: The formula places data in account 6100.14 (this should be in column C); 6100.15 (there is no matching data in the lookup table for this account);
and 6200.13 (ther is no matching data in the lookup table for this account either).

The way the formula has been applied in the different cells makes no sense to me.

Robert


RE: Promblem with Access data imported into excel

You've got TWO lookups.
1) The Account/ProspectNo

AcctNo	ProspectNo
6100.12	1
6100.14	1
6200.11	1
6100.1	2
6100.12	2
6100.17	2
 
...but this on has a problem: you can NEVER get to the second set of ProspectNo (2) -- NEVER! What is the 2 vs 1 for?

2) The Account/ProspectName

Column1 	SumOfCost
6100.12Heavenly	$332.22
6100.14Heavenly	$3,594.22
6200.11Heavenly	$1,500.00
6100.1Niobrara	$300.00
6100.12Niobrara	$50.00
6100.17Niobrara	$33.32
 

Now if it were me, I'd just name the lookup columns by the headings using Formulas > Defined Names > Create from selection -- Names in TOP row, And then use Index & Match for the lookup.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

Furthermore, I would NOT be using an inexact match. If the account is not there, its not there.

Anyhow, here's your workbook with a solution in the IF sheet WITHOUT THE IF and with EXACT match. Otherwise it is meaningless.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

So back to the original issue regarding the format of the data coming from Access into Excel.

Where does that fit into your workbook?

Looks as if Access is generating a workbook of ProForma Invoices that you're linked to. I'd probably opt for using a query to grab the data from that workbook, rather than link to it.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

(OP)
You asked two questions:

First Q&A: This is a monthly billing invoice. The client has two (or more)separate projects (Prospects 1 and 2) that are being billed on one invoice.
The billing for each project must be kept separate; thus, Prospect 1 and Prospect 2 on the same invoice but in separate columns (C & D).

Second Q&A: Data in Access is filtered by start and end Date and appended into a table that is refreshed at each new query.. The Excel workbook is connected to the Access table and imported into a separate worksheet in the workbook.

At first, I thought the data had been corrupted during the import, but I decided it was the improper use of the lookup formulas, which you have duly noted and corrected.

I think you may have answered the question. I will attempt to apply an Index and Match lookup as you suggest and see if that will resolve issue.

Thank you very much Skip.

Robert

RE: Promblem with Access data imported into excel

(OP)
Skip:

Per your suggestion, I have applied the Index-Match lookup and it is returning text instead of the referenced cell. I have no idea how to find where the text, "AccountName," is coming from. It is not in the name manager. I have no idea why the Index-Match lookup is not functioning properly.

My lookup function: =INDEX(Table1,MATCH(A13,Statement!$D$11:$D$13,1),0)

D11:D13 is also a named range "SumOfCosts" Using named range SumOfCosts doesn't work either.

Please refer to the uploaded spreadsheet for review.

Thank you.

2Rowdy

RE: Promblem with Access data imported into excel

I have no idea why you created Tabl1e1. It is totally useless and unnecessary!

I changed the Structured Table Name of the query table to tExpRpt.

Then the formula...

=IFERROR(SUMPRODUCT((tExpRpt[AcctNo]=$A13)*(tExpRpt[ProspectName]=C$6)*(tExpRpt[SumOfCost])),0)


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Promblem with Access data imported into excel

(OP)
Skip:

In trying to create the Index-Match lookup, I wasn't able to handle the different Prospect variable using the Index-Match method. Thus, my decision to create separate tables for each prospect. I didn't create table2 in the example because I couldn't get the Index-Match lookup to work.
Glad there is a solution without having to make multiple tables.

Thank you for your help.

2Rowdy

RE: Promblem with Access data imported into excel

The key to arriving at a satisfactory solution, was seeing the data you're importing from Access. That alone dictated a different direction.

Glad I could help.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close