Promblem with Access data imported into excel 1

[rccline]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rccline]

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

 

[SkipVought]

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...
[tt]
=VLOOKUP($A13&C$6,HybredReport,2,TRUE)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rccline]

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

 

[SkipVought]

You've got TWO lookups.
1) The Account/ProspectNo
[pre]
AcctNo ProspectNo
6100.12 1
6100.14 1
6200.11 1
6100.1 2
6100.12 2
6100.17 2
[/pre]
...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
[pre]
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
[/pre]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rccline]

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

 

[rccline]

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)

D1113 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

 

[SkipVought]

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...
[tt]
=IFERROR(SUMPRODUCT((tExpRpt[AcctNo]=$A13)*(tExpRpt[ProspectName]=C$6)*(tExpRpt[SumOfCost])),0)
[/tt]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and here's your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rccline]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]