Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Combine VLOOKUP, IF, AND in one formula
Hi,
I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks |
#2
|
|||
|
|||
You can use a Pivot Table to summarize the data. There are instructions
in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Natalie wrote: Hi, I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Hi
would the following structure be acceptable: .......................Dec 04........................Jan 05 Name.........Electric.....Gas..................Ele ctric....Gas Joe Bloggs...6........................................ .............10 Jemma............................................. .....5.............2 if so, it can easily be achieved from your current data using a pivot table click in your current data, choose data / pivot table and pivot chart report NEXT ensure that the whole range is selected NEXT ensure new worksheet is selected and click LAYOUT drag name to where it says "ROW" drag date to where it says "COLUMN" drag type of bill to where it says "COLUMN" ensuring it goes to the right of date drag cost to where it says "DATA" click OK click FINISH now right mouse click on Date and chose field settings, change automatic subtotals to none and you should have more or less what you're looking for. - Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Natalie" wrote in message ... Hi, I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks |
#4
|
|||
|
|||
I know how to use Pivot tables, but I want this spreadsheet to be able to
filter through to other spreadsheets, and update automaticallly. Is there any other way? "Debra Dalgleish" wrote: You can use a Pivot Table to summarize the data. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Natalie wrote: Hi, I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
I am adding this to an exsisting spreadsheet that already has information on
it, so I cannot use a Pivot table. I am trying to use: =IF(VLOOKUP(B2,'Sheet 1'!A1:F1262,5,FALSE)="Gas",AND,IF(VLOOKUP(B2,'Shee t 1'!A1:F1262,6,FALSE)="Dec-04",VLOOKUP(B2,'Sheet 1'!A1:F1262,2,FALSE))) "JulieD" wrote: Hi would the following structure be acceptable: .......................Dec 04........................Jan 05 Name.........Electric.....Gas..................Ele ctric....Gas Joe Bloggs...6........................................ .............10 Jemma............................................. .....5.............2 if so, it can easily be achieved from your current data using a pivot table click in your current data, choose data / pivot table and pivot chart report NEXT ensure that the whole range is selected NEXT ensure new worksheet is selected and click LAYOUT drag name to where it says "ROW" drag date to where it says "COLUMN" drag type of bill to where it says "COLUMN" ensuring it goes to the right of date drag cost to where it says "DATA" click OK click FINISH now right mouse click on Date and chose field settings, change automatic subtotals to none and you should have more or less what you're looking for. - Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Natalie" wrote in message ... Hi, I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks |
#6
|
|||
|
|||
Hi
Let's the first table be on Sheet1 with headers in row 1, and second table on Sheet2, also with headers on row 1 (both tables staring from column A). As first step, modify either entries in Date column on Sheet1, or headers on Sheet2, so date text are identical. I.e. or you have dates in form "Jan05" etc., or your headers are like "GasDec 04". Now, into cell B2 on Sheet2 enter the formula =SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=SUBSTITUTE(SUBSTITU TE(B$1,"Gas",""),"Electric","")),--(Sheet1!$C$2:$C$5=SUBSTITUTE(B$1,SUBSTITU TE(SUBSTITUTE(B$1,"Gas",""),"Electric",""),"")),Sh eet1!$D$2:$D$5) and copy the formula into range B2:E3 -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Natalie" wrote in message ... Hi, I have one worksheet with information for lots of people, but on separate rows - Name Date Type of Bill Cost Joe Bloggs Jan 05 Gas £10 Joe Bloggs Dec 05 Electric £6 Jemma Jan 05 Gas £2 Jemma Jan 05 Electric £5 I want to add them to a new sheet, which is set out as: Name GasDec04 GasJan05 ElectricDec04 ElectricJan05 Joe Bloggs 0 £10 £6 0 Jemma 0 £2 0 £5 Can I use a lookup to do this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
How do I combine the 4 IF formulas into 1 formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
formula to combine cells using if/then | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |