Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Actually, the formula I gave you for Worksheet2, Cell B2 should start at B4
(per your example). -- Regards, Ron |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
I'm glad that worked for you. Thanks for letting me know.
-- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
Lookup Function - Specific Values | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions |