Lookup problem

[Darrell]

Hello all,

I have been assigned a task that I am unfamiliar with. I
am working with a check sheet, to be linked of course to
the rest of the workbook. My check sheet needs to
include, let us say overbudget amounts, 'if' they exist.
There are maybe 50 line items that "could" be over budget
at any point in time. I need to figure out a way to pull
this information into the check sheet, but not showing all
50 lines. In that case, it would be very easy with an IF
statement.... but my boss wants to show the information in
as many rows as there are overbudget amounts. I hope that
this is a good enough description for advise!

Thanks!

 

[BenjieLop]

I will attempt to interpret your problem ...

I am assuming that your 50 line items are all in Column A and that th
overbudget amounts will be shown in Column B? Is this correct?

Also, how do you know if any items in Column A are overbudget? Do yo
compare these with another number in another cell?

 

[Guest]

Correct - the cost codes would be located in Column A, and
lets say that the overbudget numbers are in column B (but
more like column K).... The Overbudget amount is
calculated by % of completion, which compares to the
budget amount - both in seperate cells. So - if a job was
at 75% complete overall, and labor was 90% complete, the
overage (15% of labor budget) would be calculated to show
the overbudget amount - which in my case could not be
allowed to be drawn from the capital providers.

Any help would be GREAT!!
Darrell

 

[BenjieLop]

OK ... my suggestion here is based on how I understand your
presentation. You say that the overbudget numbers are in Column K and
already calculated.

So, assuming that your cost codes are in Column A (A2:A52) and that the
calculated amounts (to determine overbudgets) are in Column K (K2:K52),

Enter in Cell B2 and copy down until Cell B52,

=if(K2>0,"Overbudget","Still have money to spend")

Will this help?

Correct - the cost codes would be located in Column A, and
lets say that the overbudget numbers are in column B (but
more like column K).... The Overbudget amount is
calculated by % of completion, which compares to the
budget amount - both in seperate cells. So - if a job was
at 75% complete overall, and labor was 90% complete, the
overage (15% of labor budget) would be calculated to show
the overbudget amount - which in my case could not be
allowed to be drawn from the capital providers.

Any help would be GREAT!!
Darrell

 

[Guest]

This is the problem - my boss is not wanting to have 50
cells of information. There are never 50 overbudget items
at once - we can only hope - therefore she wants to have
the information from the 50, pulled into a smaller
numberof cells - say the actual number that is overbudget
at that time. I have been thinking about this for a
couple of days, and truly believe that it can not be
done. What do you think?

Thanks in advance,
Darrell

 

[Guest]

I am not seasoned in Macros - but could try get up to
speed. Do you know if a User Defined Macro may be written
to solve this problem?

 

[BenjieLop]

This (rewritten) formula

=if(K2>0,"Overbudget","")

copied down to suit your range will not give you 50 cells o
information. At a glance, you can tell which items are overbudge
because the cell opposite the overbudget item will have "Overbudget
entered. The cells opposite items within budget will be left blank.
So, if you have, say, 4 items overbudget, you can readily pinpoin
what/where they are.

If you prefer, your formula can be

=if(K2>0,"**********","")

so that the overbudget items can be identified by cells which hav
"**********" opposite them.

I cannot really help you on UDF as my "confidence" is still shaky i
this area.

Perhaps the real experts can pick up on this thread and take it fro
here.

This is the problem - my boss is not wanting to have 50
cells of information. There are never 50 overbudget items
at once - we can only hope - therefore she wants to have
the information from the 50, pulled into a smaller
numberof cells - say the actual number that is overbudget
at that time. I have been thinking about this for a
couple of days, and truly believe that it can not be
done. What do you think?

Thanks in advance,
Darrell