index v. match

[Legacy 163032]

hi,

i wonder if you could help.

i want to use the index/match function to extract vertical values and place them horizontal using a Gant Chart.

say week 1 of month January, then week two of January and so forth, matching the values as per list dates on the back, not forgeting to match them by contract.
example:

a: has the contract no.
b: has the application issue date, and
c: has the value


on my summary page (worksheet1) i want to extract i.e

the list of A: column from workbook 2 which has the contract no. by removing duplicates.

and then, get the values from the same list (C and through them under the relevant week number of that contract.


basically to me, it sounds that i might have to do vlookup and then index match. but i don't know how.
every time i use the index match function - it only add the values once and the following week value is not added from the list into the chart.

anything you may recommend i do?

many thanks#
ferdie

 

[Gerald Higgins]

Hi, welcome to the board.

It looks to me as if your request is far too vague to get a useful answer.

Can you be really really clear please about what exactly you need help with ?

It might be useful for you to take a look at Excel's help information on the MATCH and INDEX functions.

 

[Legacy 163032]

Gerald you are absolutely right - sorry about this. perhaps i have expressed my mind here and not the right definition. that is what you get when you spnd most of your time glued to excel. lol


basically what i am trying to achieve is that, i have a list of applications that i generate each month - say i log them into worksheet 2.

say recorded on column a: will have the contract number of that application, b: will have the date issued and c: will have the value. and d: will convert the date into weec comencing - so later can match with the week showing on the gant chart.

but each job can have 3 - 6 applications, therefore on worksheet 1, i only will need to ectract the contract number only once from the list on sheet two but apply the values (different applications raised for that particula contract ) across the gant chart to match the specific week number. - (i hope you are with me so far)


below i have done an example:

a separate column has been added to the list to extract the dates into week commencing of that week of the month.

here is an example what i want to achieve - i could have sent you an excel attachments but don't know how to do it in here - as i'm new but regularly follow the tips from the site.:

sheet two - the list

a b c d e
cont. No Appl. No. Date Value
1170 1 5/1/11 £2,500 week 1
1189 1 13/4/11 £4,500 week 15
1170 2 14/1/11 £3,600 week 5
etc


now i would want them to be shown as this:

gant chart
a b c d etc
W/C: Week 1 week 2 week 15

1170 £2,500 £3,600
1189 £4,500

etc

i hope you get the picture what i want excel to do for me.

i have the formula to convert the date into week commencing, but now i want the index match to locate the values from the list (sheet two) into a gant chart under the week given.

thanks

 

[Legacy 163032]

Gerald

i thought i could draw an example into the thread - but it comes all mixed up, neither is giving me any option to attach a file - otherwise it would have been 1 minute adjustment into the existing formula.

thanks anyway

 

[Legacy 163032]

in trouble and desperate to get this working..

match/index function how does it work to recall all values - say i want all the values on the data base to be shown on the gant chart appendix 1

Appendix 1

----A---------------------B-----------C-------------D--------------E
A1= Wk commencing____[4/4/11]____[11/4/11]____[18/4/11]____[25/4/11]
A2= Activity1__________[£760]__________________[£1500]_______[£350]_
3= Activity 2______________________[£350]_________________________
4= Activity 3__________[£550]________[£259]_____[£450]_____________
5= Activity 4__________[£1546]__________________[£460]_____________



datasheet, where data is being extracted has the following

appendix 2
list of the activities
----A---------------------B-----------C-------------D--------------E
A1= Description______WK Comm______Value_

A1 = Activity 1 ______4/4/11_________760
A2 = Activity 2 ______11/4/11________350
A3 = Activity 3 ______4/4/11_________550
A4 = Activity 3 ______11/4/11________259
A5 = Activity 3 ______18/4/11________450
A6 = Activity 4 ______4/4/11_________1546
A7 = Activity 4 ______18/4/11________460

i have the following formula. how do i get the match/index formula to offset the values of that activity carried out each month of week.

at the moment i have this formula =IF(B$1=Database!$B:$B,INDEX(Database!$C:$C,MATCH($A1,Database!$A:$A,0)),"")

so far this work fine, but it does not add anymore values other than once.

my question will be - how to make all the value appear under their week commencing indicated in both database and gant chart.

i must say - this is crucial and is cosing so much of a distress above all - as i know i am currently there - but need to add another match index i guess but i don't know where?

although i do need your support and i thank you for it.

deferdina

 

[Legacy 163032]

how to use Index/match function to call multiple values?

in trouble and desperate to get this working..

match/index function how does it work to recall all values - say i want all the values (appendix 2) currently on the database to be shown on the gant chart which is appendix 1 below?

Appendix 1

----A---------------------B-----------C-------------D--------------E
A1= Wk commencing____[4/4/11]____[11/4/11]____[18/4/11]____[25/4/11]
A2= Activity1__________[£760]__________________[£1500]_______[£350]_
3= Activity 2______________________[£350]_________________________
4= Activity 3__________[£550]________[£259]_____[£450]_____________
5= Activity 4__________[£1546]__________________[£460]_____________



datasheet, where data is being extracted has the following

appendix 2
list of the activities
----A---------------------B-----------C-------------D--------------E
A1= Description______WK Comm______Value_

A1 = Activity 1 ______4/4/11_________760
A2 = Activity 2 ______11/4/11________350
A3 = Activity 3 ______4/4/11_________550
A4 = Activity 3 ______11/4/11________259
A5 = Activity 3 ______18/4/11________450
A6 = Activity 4 ______4/4/11_________1546
A7 = Activity 4 ______18/4/11________460


at the moment i have this formula =IF(B$1=Database!$B:$B,INDEX(Database!$C:$C,MATCH($A1,Database!$A:$A,0)),"")

so far this work fine, but it does not add anymore values other than once.

my question will be - how to make all the value appear under their week commencing indicated in both database and gant chart.

i must say - this is crucial and is cosing so much of a distress above all. i sense the hard work is over and nearly there- but still need a soft touch round the edges..... perhaps to add another match index (which i don't know where)?

although i do need your support and i thank you for it.



number kruncher- hi,

i have been transferred into my initial post (which to start with, i wasn't very good in posting the message). anyhow!!!!

getting back o your question - yes i do use the excel 2007 although at work i use 2010 but hope this will have no implication.
regarding the appendix 1 and appendix 2 as to why they cant be in the same sheet - is basically the template i have created has much more formulas on either sheet - but for the final presentation i only needed to extract these data into the appendix 1.

so i have the values in form of chart and not as a lis (which it can get confusing if you have a big list).

thx

 

[Legacy 163032]

Re: how to use Index/match function to call multiple values?

in trouble and desperate to get this working..

match/index function how does it work to recall all values - say i want all the values (appendix 2) currently on the database to be shown on the gant chart which is appendix 1 below?

Appendix 1

----A---------------------B-----------C-------------D--------------E
A1= Wk commencing____[4/4/11]____[11/4/11]____[18/4/11]____[25/4/11]
A2= Activity1__________[£760]__________________[£1500]_______[£350]_
3= Activity 2______________________[£350]_________________________
4= Activity 3__________[£550]________[£259]_____[£450]_____________
5= Activity 4__________[£1546]__________________[£460]_____________



datasheet, where data is being extracted has the following

appendix 2
list of the activities
----A---------------------B-----------C-------------D--------------E
A1= Description______WK Comm______Value_

A1 = Activity 1 ______4/4/11_________760
A2 = Activity 2 ______11/4/11________350
A3 = Activity 3 ______4/4/11_________550
A4 = Activity 3 ______11/4/11________259
A5 = Activity 3 ______18/4/11________450
A6 = Activity 4 ______4/4/11_________1546
A7 = Activity 4 ______18/4/11________460


at the moment i have this formula =IF(B$1=Database!$B:$B,INDEX(Database!$C:$C,MATCH($A1,Database!$A:$A,0)),"")

so far this work fine, but it does not add anymore values other than once.

my question will be - how to make all the value appear under their week commencing indicated in both database and gant chart.

i must say - this is crucial and is cosing so much of a distress above all. i sense the hard work is over and nearly there- but still need a soft touch round the edges..... perhaps to add another match index (which i don't know where)?

although i do need your support and i thank you for it.



number kruncher- hi,

i have been transferred into my initial post (which to start with, i wasn't very good in posting the message). anyhow!!!!

getting back o your question - yes i do use the excel 2007 although at work i use 2010 but hope this will have no implication.
regarding the appendix 1 and appendix 2 as to why they cant be in the same sheet - is basically the template i have created has much more formulas on either sheet - but for the final presentation i only needed to extract these data into the appendix 1.

so i have the values in form of chart and not as a lis (which it can get confusing if you have a big list).

thx

hi,

thanks for your formula earlier regarding the match/index function.

that it worked. i have been nearly all day on it today trying to work out as to why did it not work. and the answer was that, i had not selected the same number or rows for the column. kept bringing me the value error.

now all done and dusted. although, this was an attempt when i had to copy all the list into the same worksheet.

appendix 2 (worksheet 1) and appendix1 into same worksheet)

now i have to test them separately.

thanks again.

ps; yes my excel sheet is 2007

deferdina