Lookup Query

Hi

I am attempting to attache the file but it will not allow me. It contains a monthly worksheet which contains a standard matrix/tble. Sheet 4 i intend will be the sheet that looks up the values in the matrix and the monthly trend sheet will display these in a grph.

I can do a V-lookup formula, but the problem arises if the matrix changes i.e. if columns are added/deleted etc then the vlookup does not update. Can anyone help me with a formula/solution to my problem.

Thanks a lot
Steve

Instead of using a range like A1:C15 in the lookup, you can use a defined name and put a dynamic formula into this name.

If your lookupformula is like this:

Please Login or Register  to view this content.

change this to:

Please Login or Register  to view this content.

Then define the name LookupRange with a formula like this:

Please Login or Register  to view this content.

this gives you a range starting in A1, 3 columns wide, and with the number of rows equal to the number of cells in column A that are not empty.

If you want to attach your excelfile you have to put it into a .zip file first.

Hope this helps.

i have now attached the report Please could you demonstrate the solution. or recommend a better approach.

Thnks

This is the modifications i have made to make it work:
Put a reference to the worksheet names in row 1
Put a reference to the service name in column A (new column)

Then this formula is entered into C4. You can copy this to all the other cells that needs a lookup function.

Please Login or Register  to view this content.

If you add columns or row to your source data the formula should still work, as long as you don't exeed the range A1:Z100. If you do, then change this in reference in the formula.

The next thing you could look into is making a dynamic X-axis on your charts, so you dont have to show the months that still not contain any data. I'm sure you can find links about this on this forum, but we take it one step at a time. See if this have helped you on your way first.

As a side note i would say that the best thing would be to have all your source data i one sheet, and arrange it in a way that lets you use database functions, pivot tables and pivot chart. That would make your analysis possibilities a bit more flexible.

Thanks a lot for this, it works brilliantly, better than i ever imagined. This is exactly what i was after.

The thought did cross my mind to utilise pivot tables and pivot charts for this data, but the layout of this report is not my decision (managements decision) and it is required to be in a monthly worksheet layout.

I suppose what i could do though is create a YTD worksheet and put a column "month" in there. So that evry month when the matrix is updated it gets copied into the YTD worksheet with the actual month in the column, allowing me to use pivot table analysis.

I have just noticed one small problem, i tested the formula by entering some additional columns and rows and entered values in there. The formula worked up until column Z but on column AA it did not. Do you know any reason for this and if so the solution.

I may also have a few addidtional things extra tmrw because there are to be new values that we are tracking in this report.

Really appreciate everything. Thanks a lot. If you could just let me know an answer to the problem, cheers

If you look at the range of the lookup that Bjornar gave you, you will see that it runs from A1 to Z100. You will need to change the Z100 to whatever you need to cover your lookup range.

See ya,

Dean

Apologies, i misread it. After i posted that comment i realised that it said to Z100 i originally thought it said AZ100. Cheers anyway, good spot.

Hi

Thanks for everything Bjornar. There are some additional requests i have if you are able please. As i mentioned there are some other things tht are required in the report. in the file attached There are seven types of resources Service control - Monitoring (Cells R-AO in the matrix). They can either be Offshore, EE or Onshore.

What i require is to somehow have a pie chart for each resource for the series type Onshore offshore and EE. There are going to be more services added to the matrix probably around 30 so it would be far to time consuming to produce a graph for every resource for every service (around 200).

What i would like is some automatic approach to this or any other solution. The graph will somehow have to only show the data for the last month where there are values.

Does anyone know any solution for this.

Really Appreciate the help Bjornar. You have saved me a lot of manual time. Thanks a lot.

Can anyone help with the above, please.

it will have to recognise what the last month was.

thanks

In the attached file i have created a new sheet because i require two sheets of graphs. One that will show all services on one page and one where the user can select the service from a drop down list so it saves time scrolling down.

I have copied sheet 4 to sheet 1 (the sheet Bjornar designed). and have entered that cells in A=A1 i.e. drop down list. For some reason this has not worked. Can anyone please explain why and how i can solve the situation

Thanks