IF alternative or additional formula

[kelvin156]

Hi,

I have some spreadsheets set up to track payments, Spreadsheet 1 shows ALL companies with Spreadsheets 2,3,4+ set up to track the individual companies.

On spreadsheet 2,3,4 ETC I am using a simple =IF('All Tracker'!B6="Company1",'All Tracker'!B6," ") - this is then duplicated across to column P to pick up other details

Although this works and picks up the data i need i would like the data when it enters the individual company spreadsheet to automatical fill down from the top of the speadsheet and not it the corresponding location to where it is in the ALL spreadsheet.

I understand i could probable just copy and paste the date to the top of the sheet but there are around 15 sheets and i am sure there will be a way to do this.

Thank You.

[ChemistB]

Create a helper column on your "All Tracker Sheet" You can hide this later if you like
Assuming data starts in row 6, I put my helper column in K, In K6 copied down

=B6&COUNTIF($B$6:$B6,B6)

On the individual sheets, Lets' say for Company A

=IFERROR(INDEX('All Tracker'!$B$6:$B$25,MATCH("Company A"&ROWS($A$1:$A1),'All Tracker'!$K$6:$K$25,0)),"")
modify the formula for all columns of data you wish to pull over.
See example

[kelvin156]

ChemistB
Thank You, i am however experiencing a few problems.
My amended formula, in B6, to take into account my ranges is =IFERROR(INDEX('All Tracker'!$B$6:$B$1000,MATCH("Company 1"&ROWS($A$1:$A1),'All Tracker'!$A$1:$A$1000,)),"") this brings in the first 6th line of Company 1's data , it misses out the first 5 rows, when copying down it shows the correct number of lines for company 1, which is 11, but shows 5 lines of data from Company 2.

I will try and anonomise the data and attach it shortly to show you.
Thanks Again.

[kelvin156]

I am having an issue anomonisng the data in a seperate spreadsheet it then brings blank cells back, i will try again later. Thanks

[FDibbins]

I noticed a small difference in your formula to CB's....
=IFERROR(INDEX('All Tracker'!$B$6:$B$1000,MATCH("Company 1"&ROWS($A$1:$A1),'All Tracker'!$A$1:$A$1000,)),"")

This may be causing your problem - your ranges are not the same

[kelvin156]

FDibbins, thanks.

I dont think that will be the issue, the ranges are different on my formala as the range of data in my spreadhseet is larger and my helper column is located in A instead of K.

[kelvin156]

Here is some anonomised date i have in my 'All Tracker'.

[FDibbins]

Sorry to be a pain, but could you provide a few sample answers on this? I see where you want he answers (I think), but cant see what you want teh extract based on

[kelvin156]

I have now attached what i would be looking to acheive with all of the data copied to the relevant matching companys seperate sheet.

Thanks

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.
Assuming that as shown in the attached sheet in post#9, all the sheets, excluding All Tracker sheet, contain company name in B1, then

In B6

Please Login or Register  to view this content.

and then copy across and down as far as needed.

Is this what you are trying to achieve?

[ChemistB]

FDibbins was correct. The reference to Col A needs to start with row 6

=IFERROR(INDEX('All Tracker'!B$6:B$1000,MATCH("BE LTS"&ROWS($A$1:$A1),'All Tracker'!$A$6:$A$1000,)),"")

I also removed the anchors ($) from in front of the B6:B1000 so that when you drag it to the right, it pulls the next column.

I created a new tab "BE LTS 2" to show you results with the formula. (placed in A2 and copied across and down)

[kelvin156]

SKTNEER & CHEMISTB -Thank you for your latest replies, both give me the desired out come.

[sktneer]

You're welcome and thanks for the feedback.