index/match return data from one sheet to another

**pauldaddyadams** · 10-06-2012, 04:42 PM

Hi All
Can anyone help me with a formula please...

I am unsure if this will be an index/match or sumproduct formula or even something else.

I have a “cost trend” sheet which I need a formula in the red highlighted cells which will pull data from the summary tab.

These are the matches:

Cost Trend Sheet match rows 5,17 and 29 with cell B1 on the “Summary” sheet (Match the Month)
Cost trend sheet match rows 6, 8 and 30 with columns C and D on the “Summary” sheet (Match the Actual or Budget)
Cost Trend sheet match column C with column B on the “Summary” sheet

The formula will only work for one month at a time – it needs to be zero if it’s a different month to cell B1 on the “Summary” sheet

Any suggestions how best to achieve this? I can not change any data on the “Summary” sheet.

**FDibbins** · 10-06-2012, 05:20 PM

in D7, use this formula
=IF(D$5=Summary!$B$1,INDEX(Summary!$B$10:$E$136,MATCH($C7,Summary!$B$10:$B$136,0),2),"")
in E7, use this 1
=IF(D$5=Summary!$B$1,INDEX(Summary!$B$10:$E$136,MATCH($C7,Summary!$B$10:$B$136,0),2),"")
copy this pair to all areas

let me know you you make out

**pauldaddyadams** · 10-06-2012, 05:32 PM

Hi,

I changed the second formula to:

Please Login or Register  to view this content.

to pick up the budget file.

These formulas work for "Branch1" however when I copy it down to Branch2 and Branch3 it doesnt change - is there a way to match the branch?

**FDibbins** · 10-06-2012, 06:31 PM

oops ok sorry about the 2 that should have been a 3, and i didnt notice your sheet2 had 3 different branches

see if the attached gives you what you need?

**pauldaddyadams** · 10-07-2012, 05:00 PM

Hi,

This is soo frustrating... The formula works perfectly in the file you uploaded but when I pasted it accross to my file I get the error message #REF

Can anyone see what I am doing wrong? Example attached

**Ace_XL** · 10-08-2012, 01:45 AM

You do not have the defined name ranges (Branch1, Branch2, Branch3) in the file you've just uploaded.

Define these and the formula will work

Branch1=Summary!$B$10:$G$46
Branch2=Summary!$B$55:$G$91
Branch3=Summary!$B$100:$G$136

**pauldaddyadams** · 10-08-2012, 05:22 AM

Hi

I never thought to look there - if I had i would have saved 2 hours of my time, you live and learn!!

Its highlighted a new problem...

One of my sheets is called "Basingstoke IGU" however the named range can not contain spaces. Is there any way I can call the named ranged "BasingstokeIGU" but adapt the formula below to add the space?

Please Login or Register  to view this content.

Can that be done?

**Ace_XL** · 10-08-2012, 05:32 AM

Cull out the spaces in the indirect. Hence, use ..

Please Login or Register  to view this content.

**pauldaddyadams** · 10-08-2012, 06:08 AM

Thanks ACE_XL - that did the job

What part of the formula actually refers to the named range?

**Ace_XL** · 10-08-2012, 06:12 AM

This gives you the defined name or 'Basingstoke IGU'
INDIRECT("C"&ROW()-COUNTA($D$6:$D6))

This gives the INDIRECT of the named range without the spaces
INDIRECT(SUBSTITUTE(INDIRECT("C"&ROW()-COUNTA($D$6:$D6))," ",""))

index/match return data from one sheet to another

LinkBack

Thread Tools

Rate This Thread

Display

index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Re: index/match return data from one sheet to another

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions