Excel Help please

**adrianh** · 14-Aug-10, 11:00 PM

Originally posted by 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

Ok, here is a demo

Code:

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, 11:35 PM

Originally posted by adrianh

Ok, here is a demo

Code:

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

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

**GrantD** · 01-Oct-12, 09:04 PM

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** · 01-Oct-12, 11:25 PM

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** · 02-Oct-12, 06:31 AM

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** · 02-Oct-12, 06:38 AM

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** · 02-Oct-12, 08:31 PM

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** · 02-Oct-12, 08:48 PM

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** · 03-Oct-12, 07:16 AM

See attached example

Sample.xlsx Sample.xlsx

**adrianh** · 03-Oct-12, 09:59 PM

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** · 04-Oct-12, 09:19 AM

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** · 03-Aug-14, 03:12 PM

Excel help needed

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** · 03-Aug-14, 04:08 PM

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** · 03-Aug-14, 04:29 PM

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.

Excel Help please

[Question] Excel Help please

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment