IF statement help

[J HART]

Good Morning/Afternoon,

Was hoping for help with a complicated (to me!) excel issue. Seen others
use animals to try and explain there issues so will do the same, hope it
doesn't come off as patronising!

Basically I have reports sent to me with data, but if there is 0
quantity for an item it will not show up on the report.

For example if I got a report for the following:

Dogs - 1
Cats - 3
Birds - 0

Then the 'Birds - 0' entry (birds and 0 in seperate cells) would not be
in the report, but in the spreadsheet I am compiling I would need the
fact that there are no Birds represented.

At the moment I copy all the report data into an export sheet.

I need a forumula which will look if there is a cell with the word
'Birds' in it in the export sheet, and if so get the number from the
adjacent cell in the main sheet. If not then put a 0 in the adjacent
cell in the main sheet.

Played around with IF statements but couldn't figure this one out.

Many thanks for any help!




--
J HART

[Claus Busch]

Hi,

Am Thu, 16 May 2013 09:13:36 +0100 schrieb J HART:

> For example if I got a report for the following:
>
> Dogs - 1
> Cats - 3
> Birds - 0

animals in sheet Main and also in sheet Export in column A and the
quantity in column B.
Then in sheet Export B1:
=IFERROR(VLOOKUP(A1,Main!$A$1:$B$3,2,0),0)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[J HART]

Claus Busch;1611861 Wrote:
> Hi,
>
> Am Thu, 16 May 2013 09:13:36 +0100 schrieb J HART:

> -

> > For example if I got a report for the following:
> >
> > Dogs - 1
> > Cats - 3

> > Birds - 0-

>
> animals in sheet Main and also in sheet Export in column A and the
> quantity in column B.
> Then in sheet Export B1:
> =IFERROR(VLOOKUP(A1,Main!$A$1:$B$3,2,0),0)
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

Thanks for your reply.

Will clarify my situation a little sorry for being vague.

In MAIN sheet I have

Column A----Column B
dogs--------Quantity (0 if no figure)
cats---------Quantity (0 if no figure)
birds---------Quantity (0 if no figure)


In EXPORT I have:

Column A----Column B
dogs---------3
Cats----------1


No row for birds because there are none in the imported report. Also the
order of the entries can change in the export sheet. So even if in 2
separate weeks there are quantise for dogs they may not necessarily be
in the same cell both weeks.

Will your given formula, look for a 'birds' entry in column A in EXPORT
and if so then fetch the quantity from the adjacent cell in column B
from EXPORT sheet and put it in column B in MAIN sheet, and if there is
no birds cell in column A in EXPORT then will it put a 0 next to the
birds cell in MAIN.

Sorry if I'm making this sound more complicated than it is, at first it
seemed like quite a simple problem to me but I can't figure out a
working formula for it.

Many Thanks




--
J HART

[Claus Busch]

Hi,

Am Thu, 16 May 2013 11:24:46 +0100 schrieb J HART:

> In MAIN sheet I have
>
> Column A----Column B
> dogs--------Quantity (0 if no figure)
> cats---------Quantity (0 if no figure)
> birds---------Quantity (0 if no figure)
>
> In EXPORT I have:
>
> Column A----Column B
> dogs---------3
> Cats----------1

in sheet Main in B1:
=IFERROR(VLOOKUP(A1,Export!$A$1:$B$10,2,0),0)
and copy down

[J HART]

Claus Busch;1611866 Wrote:
> Hi,
>
> Am Thu, 16 May 2013 11:24:46 +0100 schrieb J HART:

> -

> > In MAIN sheet I have
> >
> > Column A----Column B
> > dogs--------Quantity (0 if no figure)
> > cats---------Quantity (0 if no figure)
> > birds---------Quantity (0 if no figure)
> >
> > In EXPORT I have:
> >
> > Column A----Column B
> > dogs---------3

> > Cats----------1-

>
> in sheet Main in B1:
> =IFERROR(VLOOKUP(A1,Export!$A$1:$B$10,2,0),0)
> and copy down
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

Thanks! Worked great - modified to purpose and not to look for birds of
course.

Kind Regards




--
J HART