Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
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 Last edited by J HART : May 16th 13 at 11:28 AM Reason: Formatting |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
|
|||
|
|||
Quote:
Thanks! Worked great - modified to purpose and not to look for birds of course. Kind Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, if statement, maybe an and statement | Excel Discussion (Misc queries) | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |