lookup function HELP!
 

i use an excel workbook to track a job from start to finish, this will
include costs, client details, hours worked etc. the problem i have is with
two worksheets, the first works out all the costs (estimates) of the job and
the second produces an estimate invoice
wksheet 1
a l b l c l d l
1 l resource l supplier l estimate l estimate out l
2 l web l hype design l £300 l £500 l
3 l photo l Jimbo l £1000 l l
4 l web l Storm l £400 l l
5 l design l Karen Jones l £700 l £900 l
5 l photo l Ellis l £700 l £1000 l

wksheet 2
a l b l c l d l
1 l ESTIMATE
2 l Client details
3 l ---------------------------------------------------
4 l web l hype design l £300 l £500 l
5 l design l Karen Jones l £700 l £900 l
6 l photo l Ellis l £700 l £1000 l

i would like a function that will display in wksheet 2 ONLY the information
that has a figure in the estimate out column, i've been trying to create a
variable range based on ISBLANK for estimate out column, this will show true
if cell is blank or false when filled, based on this i would like to create a
new list with only information that has a result of false. with this range i
would then like to auto update wksheet 2 with relevant information, occupying
first available row with highest place item from the list with other items
filling as many rows as necessary. i hope this is clear otherwise i can email
an example of what i've done

cheers

There are several ways to approach your issue. This one might be a
possibility:

Worksheet1:
1)Insert a column before Col A
2)Put this formula in A2 and copy it down:
=IF(ISBLANK(E2),"",MAX($A$1:A1)+1)

Worksheet2:
1)Insert a column before Col A
2)Put this forumula in B2 and copy it across and down your report area:
=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$1:$E$20,2,0)),"" ,VLOOKUP($A2,Sheet1!$A$1:$E$20,COLUMN(B$1),0))

I think that would give you a running start at a solution.

Regards,
Ron

Actually, the formula I gave you for Worksheet2, Cell B2 should start at B4
(per your example).

--
Regards,
Ron

Sheesh...What was that comment I made a while back about not posting before
my moring coffee?

Worksheet2:
Run a numeric sequence down column A, beginning with a 1 in A2.
A2: 1
A3: 2
etc

--
Regards,
Ron

Thanks alots worked a treat

"Kwanjangnim" wrote:

i use an excel workbook to track a job from start to finish, this will
include costs, client details, hours worked etc. the problem i have is with
two worksheets, the first works out all the costs (estimates) of the job and
the second produces an estimate invoice
wksheet 1
a l b l c l d l
1 l resource l supplier l estimate l estimate out l
2 l web l hype design l £300 l £500 l
3 l photo l Jimbo l £1000 l l
4 l web l Storm l £400 l l
5 l design l Karen Jones l £700 l £900 l
5 l photo l Ellis l £700 l £1000 l

wksheet 2
a l b l c l d l
1 l ESTIMATE
2 l Client details
3 l ---------------------------------------------------
4 l web l hype design l £300 l £500 l
5 l design l Karen Jones l £700 l £900 l
6 l photo l Ellis l £700 l £1000 l

i would like a function that will display in wksheet 2 ONLY the information
that has a figure in the estimate out column, i've been trying to create a
variable range based on ISBLANK for estimate out column, this will show true
if cell is blank or false when filled, based on this i would like to create a
new list with only information that has a result of false. with this range i
would then like to auto update wksheet 2 with relevant information, occupying
first available row with highest place item from the list with other items
filling as many rows as necessary. i hope this is clear otherwise i can email
an example of what i've done

cheers

I'm glad that worked for you. Thanks for letting me know.

--
Regards,
Ron