Lookup from another sheet

  • Thread starter Thread starter Jane Doe
  • Start date Start date
J

Jane Doe

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.
 
Aladin Akyurek said:
=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.
Click to expand...

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
 
For the column which houses text values like 2005-03, invoke:

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

Adjust for the column to suit.
 
Rob van Gelder said:
Excel seriously needs a lastrow() function... these tricks look awful.
Click to expand...
....

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.
 
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
 
Back
Top