IF statement help
 

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!

IF statement help
 

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

IF statement help
 

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