OFFSET Copy&Paste Multiple Defined Named Ranges into 1 Worksheet

[INDENT]I want to merge 2+ spreadsheets (Eg: RESULTS206 and RESULTScdTi) from same workbook into 1 worksheet (MULTI-RESULTS).

Sample spreadsheet www.srands.co.uk/exoftable6.xls
Note: RESULTS206 and RESULTScdTi are sort's of WORKSHEET1, then I want a MULTI-RESULTS of all RESULTS spreadsheets.

Each spreadsheet has a named range, and the code I've entered for each of their defined name range's per RESULTS spreadsheet is:
=OFFSET($A$1,0,0,COUNTA($A:$A),1) This code expands down as Many Rows as There are Numeric and Text Entries.

Then to combine/merge all of the spreadsheets into 1 spreadsheet, copying row by row, of each RESULTS spreadsheet, TO EDIT [Blocked Image: http://www.excelforum.com/images/smilies/smile.gif]:
=OFFSET(RESULTS206!A2,0,0,COUNTA(RESULTS206!A:A),1)

=OFFSET(RESULTScdTi!A2,0,0,COUNTA(RESULTScdTi!A:A),1)

What is the right code to combine the 2 formulas above into a 1 cell formula, that works? Sample spreadsheet: www.srands.co.uk/exoftable6.xls

Guess this is straight forward, can't find any decent obvious examples, odd as surely many people encounter something like this on a daily basis.

What I tried already :
~ Pivot tables, the summary of figures is NOT what I need.
~ VBA code I've tried, was not of any use, as would only copy&paste raw text and/or number, not formula generated data.
~ RDBmerge merging doesn't provide the solution I need.
~ Data, Consolidate does NOT work with words and text, only numbers.

CROSSTHREAD(S):
http://www.mrexcel.com/forum/showthread.php?t=632777
http://www.excelforum.com/exce…ges-into-1-worksheet.html[/INDENT]

Re: OFFSET Copy&Paste Multiple Defined Named Ranges into 1 Worksheet

I've resolved this myself with INDEX MATCH, essentially my workbook http://www.srands.co.uk/exoftable6.xls merges the RESULTS spreadsheets in MULTIRESULTS, which is great. The only comment I could make is that it's not in the order I'd prefer it in, the email order doesn't really matter, you'll see, later.My code copy&pastes as per data source entry (WORKSHEET), instead of per sorted RESULTS in spreadsheets RESULTS206 and RESULTScdTi, guess this is possible with OFFSET but nobody has replied to say if this is mission impossible, or possible, and how. Anyway it works, as follows: 'WORKSHEET' with 'predefined criteria' for

~ RESULTS206
Column AF

=IF(AND(A2=0),"",IF(AND(G2="",OR(B2=206), H2="Y",I2="Y",J2="Y",K2="Y",L2="Y",M2="Y",N2="Y",O2="Y",P2="Y",Q2="Y",AC2="Y",AD2="Y"),"Y","N"))

Column AG

=IF(A2="","",COUNTIF(AF$2:AF2,">="&RESULTS206!$U$2)-COUNTIF(WORKSHEET!AF$2:AF2,">"&RESULTS206!$U$3))

~ RESULTScdTi
Column AI

=IF(AND(A2=0),"",IF(AND(G2="",OR(B2="civic"),H2="Y",I2="Y",J2="Y",K2="Y",L2="Y",M2="Y",N2="Y",O2="Y",P2="Y",Q2="Y",AC2="Y",AD2="Y"),"Y","N"))

Column AJ

=IF(A2="","",COUNTIF(AI$2:AI2,">="&RESULTScdTi!$U$2)-COUNTIF(WORKSHEET!AI$2:AI2,">"&RESULTScdTi!$U$3))

~ MULTI-RESULTS (Of all results sheets)
Column AL

=IF(AND(A2=0),"",IF(OR(AF2="Y",AI2="Y"),"Y","N"))

Column AM

=IF(A2="","",COUNTIF(AL$2:AL2,">="&MULTIRESULTS!$U$2)-COUNTIF(WORKSHEET!AL$2:AL2,">"&MULTIRESULTS!$U$3))

Then on 'SEPERATE' RESULTS SPREADSHEETS, and the COMBO 'MULTI-RESULTS':
~ RESULTS206
Cell U4

=MAX(WORKSHEET!$AG:$AG)

Cell A2 etc

=IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AG:$AG,0)))

~ RESULTScdTi
Cell U4

=MAX(WORKSHEET!$AJ:$AJ)

Cell A2 etc

=IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AJ:$AJ,0)))

~ MULTI-RESULTS (Of all results sheets)
Cell U4

=MAX(WORKSHEET!$AM:$AM)

Cell A2 etc

=IF(ROWS($A$1:$A1)>$U$4,"",INDEX(WORKSHEET!A:A,MATCH(ROWS($A$1:$A1),WORKSHEET!$AM:$AM,0)))

So to my extravaganza finale (My purpose for this thread) in MULTI-RESULTS in Cell T1, left click the email symbol and emails autogenerated for all fields in MULTIRESULTS (Of all RESULTS), hooray, simple. http://www.srands.co.uk/exoftable6.xls

Re: OFFSET Copy&Paste Multiple Defined Named Ranges into 1 Worksheet

Here is my latest edit, another DATABASE version, with QUICK START macro command buttons, all with no faults for SEARCH & ADD/EDIT/UPDATE/BROWSE, even includes photo preview in SEARCH & ADD/EDIT/BROWSE for records in database with photos inserted.

http://www.1sar.karoo.net/QUICKstartFRONTdb.xls 390 KB's