Logical Formula Assistance Needed

Hi Experts

attached is a file containing annual report of four companies, i have created a formula in p&l(2) where if i select a company details pops up, i also have a formula where you can select the year to view which particular year to view the report and also the number of years you want to see the report ie you can select year 2008 and specify that you want to see 3 more yrs after 2008. my problem is how to write a formula so that the data under for instance 2008 or any other year selected will reflect correctly under that year.

pls help with the formula

Try this formula

=CHOOSE(Sheet1!$B$2,INDEX(FML!$B5:$G5,1,MATCH($H$4,FML!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(SIC!$B5:$G5,MATCH($H$4,SIC!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(GCB!$B5:$G5,1,MATCH($H$4,GCB!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(GGBL!$B5:$G5,1,MATCH($H$4,GGBL!$4:$4,0)+(COLUMN(A1)-2)))

Hi Bob, thanks for the formula, however, its not working the way i want it to.

what i want is for instance,if i select Guinness Ghana Breweries from cell b4, and also select 2008 as the start year from cell H4, and finally select 2 yrs from cell H6 ie the no. of yrs to view report, you will realise that cell b11 will have 2008, and what i expect in in cell b12 is 124848 (the revenue figure) and c11 will also read 2009 (since i selected 2yrs from cell h6) and the figure for that cell c12 will be 164441, thereafter everything shd be blank. in tht order. Hope you get what am trying to explain.

kindly help with a formula. tx again

Doesn't need much adjustment

=IF(COLUMN(A1)>$H$6,"",IFERROR(CHOOSE(Sheet1!$B$2,INDEX(FML!$B5:$G5,1,MATCH($H$4,FML!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(SIC!$B5:$G5,MATCH($H$4,SIC!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(GCB!$B5:$G5,1,MATCH($H$4,GCB!$4:$4,0)+(COLUMN(A1)-2)),
INDEX(GGBL!$B5:$G5,1,MATCH($H$4,GGBL!$4:$4,0)+(COLUMN(A1)-2))),""))

Brilliant Bob, its working to perfection, However i think you didnt see the ALL YEAR(in the select start year drop down) in the drop down so you didnt include that in he formula, by chance could you factor that in.

I have the following question:

I have about 200 records in Column 1 (name of places) consisting of letters and
In Column 2 I have about 2000 records. column 2 is superset of column 1. each record in col.2 has a numeric code written in col.3.

I wish to match the records in col.1 with that of col.2 and assign the codes contained in col.3 to the corrosponding recodrs in col.1 which should copied in col.4.

Please help . . .

nikhilesh

Originally Posted by respuzy

Brilliant Bob, its working to perfection, However i think you didnt see the ALL YEAR(in the select start year drop down) in the drop down so you didnt include that in he formula, by chance could you factor that in.

Looks like someone has hijacked your thread.

Just what does All mean in the context of a start year, by my understanding of the language that is just meaningless.