Lookup from another sheet
 

Hi,

I have my business accounts on Excel.

I have a worksheet called Hours with 5 column headers; Week Ending
(formatted as date), Invoice (formatted general), Hours (formatted custom
00), Rate (formatted currency), Total (formatted currency)

I have another worksheet called Invoice and I would like to be able to
display the contents of the last cell in each column from the Hours
worksheet so I can automatically derive the data on the Invoice sheet from
the hours sheet.

Each week I add another line on the Hours sheet and the contents will be
different in each cell again.

How do I do this please ?

Thanks

Jane xxx

=LOOKUP(9.99999999999999E+307,Hours!A:A)

if column A houses numeric data like dates, time values, or just
numbers. It will return the last numeric value from column A on sheet Hours.

Jane Doe wrote:
Hi,

I have my business accounts on Excel.

I have a worksheet called Hours with 5 column headers; Week Ending
(formatted as date), Invoice (formatted general), Hours (formatted custom
00), Rate (formatted currency), Total (formatted currency)

I have another worksheet called Invoice and I would like to be able to
display the contents of the last cell in each column from the Hours
worksheet so I can automatically derive the data on the Invoice sheet from
the hours sheet.

Each week I add another line on the Hours sheet and the contents will be
different in each cell again.

How do I do this please ?

Thanks

Jane xxx

"Aladin Akyurek" wrote in message
...
=LOOKUP(9.99999999999999E+307,Hours!A:A)

if column A houses numeric data like dates, time values, or just numbers.
It will return the last numeric value from column A on sheet Hours.

Thanks but one column doesn't work

The cell contents are 2005-03 in Hours but in the Invoice it says #N/A

This column has my invoice number like 2005-01, 2005-02, 2005-03 etc etc

Jane

Those are not numbers. They are Text.

To find last cell in Column with text.....

=LOOKUP(REPT("z",255),A:A)


Gord Dibben Excel MVP


On Sun, 23 Jan 2005 18:17:37 GMT, "Jane Doe" wrote:

"Aladin Akyurek" wrote in message
...
=LOOKUP(9.99999999999999E+307,Hours!A:A)

if column A houses numeric data like dates, time values, or just numbers.
It will return the last numeric value from column A on sheet Hours.

Thanks but one column doesn't work

The cell contents are 2005-03 in Hours but in the Invoice it says #N/A

This column has my invoice number like 2005-01, 2005-02, 2005-03 etc etc

For the column which houses text values like 2005-03, invoke:

=LOOKUP(REPT("z",255),Hours!X:X)

Adjust for the column to suit.

Jane Doe wrote:
"Aladin Akyurek" wrote in message
...

=LOOKUP(9.99999999999999E+307,Hours!A:A)

if column A houses numeric data like dates, time values, or just numbers.
It will return the last numeric value from column A on sheet Hours.


Thanks but one column doesn't work

The cell contents are 2005-03 in Hours but in the Invoice it says #N/A

This column has my invoice number like 2005-01, 2005-02, 2005-03 etc etc

Excel seriously needs a lastrow() function... these tricks look awful.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Aladin Akyurek" wrote in message
...
For the column which houses text values like 2005-03, invoke:

=LOOKUP(REPT("z",255),Hours!X:X)

Adjust for the column to suit.

Jane Doe wrote:
"Aladin Akyurek" wrote in message
...

=LOOKUP(9.99999999999999E+307,Hours!A:A)

if column A houses numeric data like dates, time values, or just numbers.
It will return the last numeric value from column A on sheet Hours.


Thanks but one column doesn't work

The cell contents are 2005-03 in Hours but in the Invoice it says #N/A

This column has my invoice number like 2005-01, 2005-02, 2005-03 etc etc

Brilliant !!!

Thanks everyone

Luv Jane

xxx

"Rob van Gelder" wrote...
Excel seriously needs a lastrow() function... these tricks look awful.
....

Excel needs 'reverse' variants of many of its functions.

MATCHREV to search from bottom to top or right to left (wild tangent: does
the equivalent of MATCH in Hebrew, Arabic or Farsi versions search from left
to right or right to left?).

HLLOKUPREV, VLOOKUPREV would also be nice, but INDEX and MATCHREV would be
sufficient.

FINDREV and SEARCHREV to search strings from end to beginning.

LEFTBACK and RIGHTBACK similar to the @LEFTBACK and @RIGHTBACK functions in
Lotus Notes.

And while I'm on pipe dreams, either make the XLM functions available as
true worksheet functions, expand CELL and INFO, or create a new function or
functions to return range, worksheet, workbook and environment properties.
FWIW, Lotus 123's @CELL function has been able to return background color,
text color, font attributes (bold, italics, underline), sheet name (even in
unsaved files, imagine that!), etc. since Release 3.0, which came out in the
summer of 1989 - 15 YEARS AGO! But it took MSFT more than 10 years to mimick
that pinacle of sophisticated spreadsheet functionality - the colored
worksheet tab - which Borland introduced in QP5 for Windows back in 1993.

There's a contrary view of what should be provided in a programmable
application development platform. Only the basics or extremely frequently
used features built in, the rest provided via add-ins. That's how most
programmable editors work (e.g., EMACS, Multi Edit, Epsilon, Brief). But
that'd mean Microsoft would think about providing another add-in providing
say, the 100 most frequently requested and rewritten UDFs provided in the
Excel newsgroups. Geez, MSFT wouldn't even need to program them. Sadly, with
regard to this don't hold your breath waiting for MSFT to provide anything
useful for spreadsheet developers. Far more likely we'll get blinking text
synchronized with MIDI or WAV playback.

You're right... MATCHREV would be great.
I'll be seeing Bill tonight so I'll definitely mention it.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harlan Grove" wrote in message
...
"Rob van Gelder" wrote...
Excel seriously needs a lastrow() function... these tricks look awful.
...

Excel needs 'reverse' variants of many of its functions.

MATCHREV to search from bottom to top or right to left (wild tangent: does
the equivalent of MATCH in Hebrew, Arabic or Farsi versions search from
left
to right or right to left?).

HLLOKUPREV, VLOOKUPREV would also be nice, but INDEX and MATCHREV would be
sufficient.

FINDREV and SEARCHREV to search strings from end to beginning.

LEFTBACK and RIGHTBACK similar to the @LEFTBACK and @RIGHTBACK functions
in
Lotus Notes.

And while I'm on pipe dreams, either make the XLM functions available as
true worksheet functions, expand CELL and INFO, or create a new function
or
functions to return range, worksheet, workbook and environment properties.
FWIW, Lotus 123's @CELL function has been able to return background color,
text color, font attributes (bold, italics, underline), sheet name (even
in
unsaved files, imagine that!), etc. since Release 3.0, which came out in
the
summer of 1989 - 15 YEARS AGO! But it took MSFT more than 10 years to
mimick
that pinacle of sophisticated spreadsheet functionality - the colored
worksheet tab - which Borland introduced in QP5 for Windows back in 1993.

There's a contrary view of what should be provided in a programmable
application development platform. Only the basics or extremely frequently
used features built in, the rest provided via add-ins. That's how most
programmable editors work (e.g., EMACS, Multi Edit, Epsilon, Brief). But
that'd mean Microsoft would think about providing another add-in providing
say, the 100 most frequently requested and rewritten UDFs provided in the
Excel newsgroups. Geez, MSFT wouldn't even need to program them. Sadly,
with
regard to this don't hold your breath waiting for MSFT to provide anything
useful for spreadsheet developers. Far more likely we'll get blinking text
synchronized with MIDI or WAV playback.

I agree about the CELL. and I would enjoy MMATCH(Lookup, Array, Position)
Position: 0 = First, 1=Last, 2=All
LASTROW (MoreFunc) should be a Microsoft Add-in.
But this could all be part of a Suggest & Vote on New Functions webpage.

Ola