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.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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!
--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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?
--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

[Earl Kiosterud]

Andy,

Lemme see if I got this right. You put a new workbook 1 (rpt_Layout 8000
Fillers and Graybar.xls), and you want any of the column E values previously
found from their associated value in B1 (because the date in column A
matched) to remain in the second workbook. It's the dynamic nature of any
formula to work with the current data being referenced. That's why they
change when the referred cells (first workbook) change. You need the data
to "latch" into the column E cells. That can be done with self-referential
cells (circular references), but isn't a great solution. A macro that puts
the hard values in place in column E is the better solution.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[green fox]

Thanks Earl...that sounds right, I'm using three other related sheets
to create a document, this is the fourth. I'm using a single VBA
routine with the other sheets so it would make sense to do this in
there. I'll see how I can incorparate what I've learned here with that
routine.

Thanks again

Andy