# General Business Category > Technology Forum > [Question] Excel Help please

## greghsa

Excel fundi's please help.
I have an Excel pricelist worksheet (.xls Excel 2003, 300 lines) with the following columns:
Code  Price1	Price2	Price3
I also have another worksheet(.xls Excel 2003, 2000 lines) with the following columns:
Code  Description

I want to modify first worksheet or create a third worksheet that will have:
Code  Description Price1 Price2 Price3

Copy and paste or sorting will not work as different number of lines.
All codes in first worksheet are in second worksheet.

I would like excel to look for same code in different worksheet and pull the description . 
Hope this makes sense. Any ideas would be appreciated

----------


## adrianh

> Excel fundi's please help.
> I have an Excel pricelist worksheet (.xls Excel 2003, 300 lines) with the following columns:
> Code  Price1	Price2	Price3
> I also have another worksheet(.xls Excel 2003, 2000 lines) with the following columns:
> Code  Description
> 
> I want to modify first worksheet or create a third worksheet that will have:
> Code  Description Price1 Price2 Price3
> 
> ...


Ok, here is a demo



```
Set up Book1 Sheet 1 as follows: (Save it and keep it open)

code	price1	price2	price3
code1	price1_1	price2_1	price3_1
code2	price1_2	price2_2	price3_2
code3	price1_3	price2_3	price3_3
code4	price1_4	price2_4	price3_4

Set up Book2 Sheet 1 as follows: (Save it and keep it open)

code	descr
code1	descr1
code2	descr2
code3	descr3
code4	descr4

Set up Book3 Sheet 1 as follows:

code                    desr	                                           price1	                 price2	                         price3
=+[Book1.xls]Sheet1!A2 	=VLOOKUP(A2,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B2       =+[Book1.xls]Sheet1!C2         =+[Book1.xls]Sheet1!D2
=+[Book1.xls]Sheet1!A3 	=VLOOKUP(A3,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B3       =+[Book1.xls]Sheet1!C3         =+[Book1.xls]Sheet1!D3
=+[Book1.xls]Sheet1!A4 	=VLOOKUP(A4,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B4       =+[Book1.xls]Sheet1!C4         =+[Book1.xls]Sheet1!D4
=+[Book1.xls]Sheet1!A5 	=VLOOKUP(A5,[Book2.xls]Sheet1!$A$2:$B$5,2,FALSE)   =+[Book1.xls]Sheet1!B5       =+[Book1.xls]Sheet1!C5         =+[Book1.xls]Sheet1!D5

Just substitute your book & sheet names, then correct your columns, rows & cell range for the vlookup.

Look carefully at the cell range, return column and exact match specifyer in the vlookup.

Cell range is top left where code starts and right bottom where description ends.
Return column is 2 because column 1 is the code to be looked up and column 2 is the description to be returned.
Exact match is set to FALSE otherwise it will return the nearest match which may not make sense. It is better to be able to convert a missing description to "MISSING DESCRIPTION" rather than a incorrect value

You can set up the missing description check as follows:

=IF(ISNA(VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE)),"MISSING DESCRIPTION",VLOOKUP(A6,[Book2.xls]Sheet1!$A$2:$B$6,2,FALSE))

Remeber to correct all the cell references
```

...and there you go

----------

greghsa (14-Aug-10)

----------


## greghsa

> Ok, here is a demo
> 
> 
> 
> ```
> Set up Book1 Sheet 1 as follows: (Save it and keep it open)
> 
> code	price1	price2	price3
> code1	price1_1	price2_1	price3_1
> ...


I new it would have a VLOOKUP in it, but would never have cracked it myself. Thank You

----------


## GrantD

Hi Guys 

Have a question on excel:

I have a created a table on excel with coloumns and rows. 

I effectively need to find the sum of a criteria that is required in Coloumn A and row A ie 

  A B C D E F G H

1
2
3
4
5
6
7

----------


## adrianh

You need to explain what you need far more clearly than that...either that or I am to doff to figure out what you are on about.

----------


## GrantD

Hi, Apologises the thread uploaded before I finished the Q:

I have an array of data. In the top row , row 1, I have the following consistent cell descriptions

T1Actual	T1Budget	T1Prior		T1Actual	T1Budget	T1Prior

In the first coloumn, col A i have the following data


A8


A3


A8
A8

A6

What i am trying to calculate is the sum of all the points in the table where T1Actual and A8 intersect.
The 3 parts of the formulae are effectively
Table A1: Z50
Criteria 1 : Row1, "T1Actual"
Criteria 2 : Coloum A, "A8" 

Now sum where all these points meet in the table.

Hope this make more sense

Thanks

----------


## adrianh

Why don't you just sort all the data by t1actual. Doing so will group all the A8's together making it easy to sum.

----------


## GrantD

each 3 refrences refers to a month in 2012 ie Jan then Fe then march etc etc


T1Actual	T1Budget	T1Prior		T1Actual	T1Budget	T1Prior		T1Actual	T1Budget



Jan 12		Jan 11		Feb 12		Feb 11		Mar 12	
T1	T1	T1		T1	T1	T1		T1	T1

----------


## adrianh

Look, there is another way of doing it. What you would do is to write a little subroutine in VBscript that will run through your data collecting and summing the figures that you need. 

I find it very difficult to picture your data so its hard to write the program.

PM the file or a sample set of the data to me and I'll show you how to do it.

----------


## GrantD

Sample.xlsxSample.xlsx

----------


## adrianh

The best way of looking at this sort to problem is to figure out how to do it in intermediate steps. Nothing stops you from creating intermediate "work" tables to put the data into a more useable form. You could create an intermediate table that is used simply to group together the data into a more useable form. You could use vlookup to populate the table based on the criteria in column C. The trick with using Excel is to realize that you do not have to create the perfect in-cell formula in one shot. I think that when an in-cell formula becomes too complex and you are unable to evaluate the intermediate results then you are looking for trouble.

You can do it in various ways. 

option 1
Drop the subtotals.
Grab the data and sort it by Column C
Auto subtotals for every change in column C.

option 2
Drop the subtotals.
Create a new set of rows below your data and call the A1 to A8
Now create a formula in each field using =sumif(C1..C10,"A1",{whatever column you are trying to sum}
sumif is very clever because it looks a criteria to decide whether to sum the value  sumif (criteria range, criteria, sum range)

option 3
You could write a VB script to run through the data but i think it would be an overkill.

Try these methods, if you don't come right let me know thn I'll do it for you.

----------


## GrantD

Thanks for the feedback. Was hoping to do it in one magical formuale but have effectively just created 4 totals for each "T" and then used a If and nested Sumif. Seems to have worked.

Thanks again

G

----------


## Houses4Rent

I want a simple graph over my business stats over several years.

All fine, but now I want to add another set of 12m data. 
I right click on graph and SELECT DATA. That field is small and my formula is long so I just copy it, update it elsewhere, but it does not allow me to paste the updated new formula back in. Just makes a sound like when one does something not possible.

Is there maybe a limit how long a formula can be?

This works (current)
='Marc'!$E$1:$P$1,'Marc'!$R$1:$AC$1,'Marc'!$AE$1:$  AP$1,'Marc'!$AS$1:$BD$1,'Marc'!$BG$1:$BR$1,'Marc'!  $BU$1:$CF$1,'Marc'!$CI$1:$CT$1,'Marc'!$E$4:$P$4,'M  arc'!$R$4:$AC$4,'Marc'!$AE$4:$AP$4,'Marc'!$AS$4:$B  D$4,'Marc'!$BG$4:$BR$4,'Marc'!$BU$4:$CF$4,'Marc'!$  CI$4:$CT$4

but this does not work (updated):
=Marc!$E$1:$P$1,Marc!$R$1:$AC$1,Marc!$AE$1:$AP$1,M  arc!$AS$1:$BD$1,Marc!$BG$1:$BR$1,Marc!$BU$1:$CF$1,  Marc!$CI$1:$CT$1,Marc!$CW$1:$DH$1,Marc!$E$4:$P$4,M  arc!$R$4:$AC$4,Marc!$AE$4:$AP$4,Marc!$AS$4:$BD$4,M  arc!$BG$4:$BR$4,Marc!$BU$4:$CF$4,Marc!$CI$4:$CT$4,  Marc!$CW$4:$DH$4

Can anybody help?

----------


## adrianh

I spy with my little eye....lots of missing quotes around each and every....        Marc   in the second expression. Maybe that is the problem?

----------


## Houses4Rent

The copy and paste seems to have dropped a lot of things. 
I figured it out in third attempt, I had given up twice before until I asked here.
I right click SELECT DATA and then hit ENTER. That makes the formula appear on the top section of sheet and its much easier to update it there.

----------


## adrianh

hmmm...Excel formula editing is a pain butt.

----------


## Houses4Rent

The way I do it now works quite well.

----------

