lookup help

[green fox]

Second try with this...I'll try to explain it better. I have a two-cell
worksheet win workbook1. It is replaced every day, automatically with
another two cell sheet in a new workbook with the same name. A1 is a
date. B1 is number, eg. 1542.
I want to automatically place the number into a cell in another
workbook and sheet, by matching the date with dates in column A of the
second sheet, and Column E (fillers)of the second sheet.
I've been playing with match, offset and index, lookup etc. I've been
able to get a number from DATE:E and have it show up in the first
workbook.sheet, but that's the opposite of what I want to do. I can't
have the formuala in the first workbook, because it will be overwritten
each day.

I would be thankful if someone could just point me in the right
direction.

Andy

 

[Earl Kiosterud]

Andy,

In the second sheet, in the column where you want the data, try this
formula. It's coded for row 2.

=IF('[Workbook 1.xls]Sheet1'!$A$1 = A2, '[Workbook 1.xls]Sheet1'!$B$1, "")

Copy down with fill handle or copy/paste. This doesn't deal with the stuff
in column E because I didn't understand how that fits in.

 

[green fox]

Column E is where I want the number from workbook1 ($b$1) to go. For
example:

workbook1
A1=Thursday, August 18 2005 B1=1345

Workbook2
A23 = Thursday, August 18 2005 E23=0

I want the 1345 from workwook1 entered into workbook2, E23.

Thanks, sorry for the lack of clarity.

Andy

 

[Earl Kiosterud]

Andy,

Then put the formula in E2, and copy down.

 

[green fox]

I'm having trouble getting it...

=IF('[rpt_Layout 8000 Fillers and Graybar.xls]qry: filler and
graybar'!$A$1 = a19, '[rpt_Layout 8000 Fillers and Graybar.xls]qry:
filler and graybar'!$b$2, "")

i've tried a more explicit reference to workbook1 c:\layoutdocs\rpt_
etcm
the colon in the worksheet reference was mentioned in error messages,
and I've had a message about the reference to workbook not being
permited.

I can't be more specific because I tried everything I could think of
Saturday night (or was it Friday?) and finally put it down.

I'm in a complete fog, but I figure I missed something that should be
painfully obvious.

dense but determined,

Andy

 

[Earl Kiosterud]

Andy,

I don't know what all that stuff is in the link in your IF function. You
have rpt's and qry's and stuff in it. Sounds as if you're trying to refer
to Access reports and queries or something. You need only the workbook
name, sheet name, and cell reference. It should take the form:

'[Work book 1.xls]Sheet 1'!A1

 

[green fox]

The workbook was exported from access with a vba routine. The sheet
name 'qry: fillers etc., is a carry over from the access report.
Do you think that the colon in the name is the problem? If its the
naming, then I'll just change my access routing to fix it. I'm as adept
a access vba as I am at excel. argghh! Now I have to start messing with
outlook too. I love this stuff but my brain resists.

Thanks a mint.

Andy

 

[Earl Kiosterud]

Andy,

Your IF looked out of whack to me, but I see now that it's not. I should
have recognized those names. My mistake.

I think you're right about the colons. It wouldn't allow me to name a sheet
so. Your formula works if the sheet name doesn't have the colons.

The Lesynski (?) naming convention for access database objects calls for
object names like rptLayout_8000_Fillers_and_Graybar, or
qryFiller_and_graybar, but I don't recall the use of colons there.

I think you're just around the corner of the solution. Keep hacking!

 

[green fox]

Thanks again Earl,

that worked, although I think I'll have to nest it in another statement
so it doesn't wipe out the previous date value.

determined

Andy

 

[Earl Kiosterud]

Andy,

Good. I'm not sure why a date is getting wiped out. The IF returns the
data in B1 associated with the date in A1. What's getting wiped out?

 

[green fox]

Sorry, I 'mis-spoke', Column A has 31 dates -- august 1 to august 31.
Column E has the corresponding number from the other workbook. The
'else' part ot the if statement returns a blank if the dates don't
match. I need to keep the dates once they are in there. There daily
records. I haven't had a chance to look at it today -- today I'm
wrestling with using a userform to update a spreadsheet AND a userform
to grab dates and return emails. I really appreciate your help.

Thanks,

Andy