text from multiple sheets into one

[Injinia]

Hei,


I have a workbook with 40 worksheets, each with the same format. However the text in cells A3:A150 varies. I would like to put all the text from all sheets together in one sheet, so that this new sheet will have all the text in column A. so running from A1:A***


I know that the consolidation button in excel works with numbers. How can I do this in excel.


Thanks in advance.


Regards,

Injinia

 

[Injinia]

I forgot to mention also that these 40 sheets are updated every week.


-Injinia

 

[jason]

...hmm.... you could run a macro that copies the same range (A3:a150) and then pastes it into Col A of your summary sheet....


but my main question is, with these getting updated weekly, are the always the same layout and size!?! if there is text in A3:A150 one week, will that cell range always be the same placement and size?

 

[SirJB7]

Hi, injinia!


Try this in the summary sheet at cell A3 and copy down thru A447:

=INDIRECTO(DIRECCION(RESIDUO(FILA()-2;150-2)+(1-SIGNO(RESIDUO(FILA()-2;150-2)))*(150-2)+2;1;4;1;ELEGIR(ENTERO((FILA()-2-1)/(150-2))+1;"Hoja1";"Hoja2";"Hoja3"))) -----> in english: =INDIRECT(ADDRESS(MOD(ROW()-2,150-2)+(1-SIGN(MOD(ROW()-2,150-2)))*(150-2)+2,1,4,1,CHOOSE(INT((ROW()-2-1)/(150-2))+1,"Hoja1","Hoja2","Hoja3")))


Regards!

 

[Injinia]

Works perfectly! Thanks alot.


Regards,

Injinia

 

[SirJB7]

Hi, Injinia!

Glad you solved it. Thanks for your feedback. And welcome back whenever needed or wanted.

Regards!

 

[aamirsq]

Who is going to explain? whats happening here ???


=INDIRECT(ADDRESS(MOD(ROW()-2,150-2)+(1-SIGN(MOD(ROW()-2,150-2)))*(150-2)+2,1,4,1,CHOOSE(INT((ROW()-2-1)/(150-2))+1,"Hoja1","Hoja2","Hoja3")))


I seems i fall in love with formula , as i link lot of excel sheets...

 

[SirJB7]

Hi, aamirsq!


I think that nobody (and that included me) was going to do such a thing... if anybody didn't dare to ask about it.


But (as my old dog friend uses to say, there's always a but...) then you came in, so I think it's my turn, isn't it?


The workbook structure is:

- 3 data sheets, with 2 title rows (1:2) and 148 data rows (3:150)

- 1 summary sheet, with the same 2 title rows (1:2) and 148*3=444 data rows (3:446)


Summary sheet rows: 3:446

- corresponding to Sheet1, 3:150

- corresponding to Sheet2, 151:298

- corresponding to Sheet3, 299:444


For that range the formula:

MOD(ROW()-2,150-2)

returns values from 0-147 as follows:

1, 2, ... 146, 147, 0, 1, 2, ... 146, 147, 0, 1, 2, ... 146, 147, 0


Now we have the formula:

(1-SIGN(MOD(ROW()-2,150-2)))

which returns:

0, 0, ... 0, 0, 1, 0, 0, ... 0, 0, 1, 0, 0, ... 0, 0, 1

that's to say 1 when previous is zero and zero when previous is <> zero


So the multiplication by:

150-2

adds:

0, 0, ... 0, 0, 148, 0, 0, ... 0, 0, 148, 0, 0, ... 0, 0, 148


So this part:

MOD(ROW()-2,150-2)+(1-SIGN(MOD(ROW()-2,150-2)))*(150-2)+2

gives us:

1, 2, ... 146, 147, 148, 1, 2, ... 146, 147, 148, 1, 2, ... 146, 147, 148


Now:

INT((ROW()-2-1)/(150-2))+1

retrieves:

1, 1, ... 1, 1, 1, 2, 2, ... 2, 2, 2, 3, 3, ... 3, 3, 3


Then the address function:

ADDRESS(ROW,COLUMN,ABSOLUTE/RELATIVE,A1/R1,SHEET)

becomes:

ADDRESS(1-148...1-148...1-148 , 1 , 4=RELATIVE, 1=A1, "Hoja1" 148 times ... "Hoja2" 148 times ... "Hoja3" 148 times)


And finally the INDIRECT function displays the actual values.


Hope it helps.


Regards!