Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
"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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Brilliant !!! Thanks everyone Luv Jane xxx |
#8
|
|||
|
|||
"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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use lookup a value by date for criteria in Excel sheet | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |