Excel worksheet link search and fill formulas?

Hi guys,

I have a question that i was wondering if anyone knew the answer to?

I want to create a spreadsheet table of values that are linked other spreadsheets, a condesnsed version... I am not a total amateur and I understand the world of excel links....

However the problem lies in the fact that the source files change format every day, what i need is a formula for each cell (i know...) that can:

1. Firstly find column and row heading titles by looking for part of the title i.e. for a title like 'Occupation' i need a non case sensitive search for 'Occu'.

2. Once colum and row are found, return the intesecting value.

The formulas are needed so all I will have to do, after this is finished, is update the source files and the relevant numbers will be found automatically.

Im sure this is something that can be done with a clever array formula, setup to recognise column and row intersections based on positve returns on search variables, however this one is just outside my capabilities.... HELP

Oh, and i forgot to mention I do not want a VBA solution, spreadsheet must be macro free!

Have a look at INDEX worksheet function
Best wishes
Sreedhar

Thanks for that, however its it possible to build an array formula with the ROW & COLUMN fuctions inserted into the INDEX function?

Im guessing....

={INDEX(A1:C10, (ROW("Occupation")), (COLUMN("CAR")))}

?

Sorry....

Ah now that would be great if i already knew the column numbers that the information appears within, as mentioned its variable so i cant be column specific, or can MATCH be used on an array as apposed to a specific column?

I need an INDEX array that searches the whole sheet for certain requirements and returns the intersecting value.

And being honest, it would be even better if i could have a multiple query to the row number so, for example, 2 requirements must be met i.e. "origin" & "destination". And then column query is maybe "rate"

Hey Ratcat!

Ok, i'll get a simplified version over to you of what i want to do in a bit, however description of requirements are as below:

I have a central spreadsheet/matrix that i operate manually at present, it consists of vast amounts of data comprised by manually inputting information from other spredsheets, creating a very large condensed version of all of them...(with me so far?)

What i want to do is set up an 'auto updater' version of the matrix by programming each cell on the matrix to look within specific source files to return their corresponding values.

However, the information sources are all in varying formats and they often change their overall format on every update. Considering this to be the case I cannot use direct cell links only (of which would of been ideal if the formats didnt change)

What i need is a generic search formula that i can taylor to each cell link on my matrix to look for specific requirements on the other spreadsheets, and return the corresponding value.

So in example, say i need to know a postage rate from one country to another, i need to find the row in the source spreadsheet that satisfys both the origin and the destination, and then return the intersecting column which gives the rate for a certain size...

Ive looked at the INDEX function and that looks like it could work, all I need is a clever way of generating the row and column numbers without knowing what they are....(i know) by succesfully matching the search criteria (i.e. origin & destination match = Row6. Postage Rate = Column10)

Also, if possible, i need the search function to be non case sensitive, and be able to look for partial matches i.e. a search for 'England' may be 'eng'.

I know its a little complicated, but if i can get this to work, all i will need to do to update the matrix is purley change the source files through excel links. And at present that would be a god send!!

Oh, and the prob is i run this from a works machine, so no macros are aloud, i have to work within the powers of normal excel formula functionality!

I have this feeling that i know it can happen, its just i am quite rusty on excel array formulas, in words i kinda want it to:

=SEARCH(SPECIFIED SPREADSHEET) FOR (("SYDNEY" & "LONDON") = ROW6) + (("POSTAGE RATE") = COLUMN10) = INTERSECTING VALUE

Is it really that complicated!?!

HELP!

Oh and im on 2003 if that helps

Hey Ratcat

Wow! You've been busy!

Ok, so the problem with both Vlookup and Index/Match is that they rely on you knowing the column references in advance i.e. You already know where to look.

Unfortunately this is not the case, the formula required needs to search for the intersecting location based upon criteria search, not by cell specific. Like SEARCH FOR CELLS CONTAINING SYDNEY & ADELAIDE, IF THIS IS RETURNED AS TRUE THEN A ROW NUMBER WILL RESULT, AND THEN THE SAME FOR THE COLUMN... Rendering both Vlookup & Index/Matrix unusable.

Sorry, Its baking my head to! The thing that irritates me the most is that you would never have thought that it was so complicated!

Although I get the impression your enjoying this!

Ive got you stumped!

Thanks for all ur efforts ratcat,

I know, its a headache!

Ive been searching through this forum and there are some members that have pieced together some of the most complicated array formulas i have ever seen! I wonder if they can help...

If you are an array formula legend please help!