MrExcel.com - Your One Stop for Excel Tips & Solutions

Copying cell information

Posted by Jamie on August 18, 2000 10:47 PM

Hello,
I've got a file, in excel 2000, with 150 worksheets in it. What I would like to do is, on the first sheet I would like to display the contents of A1 through A4 of the second sheet through the 150th sheet IF A1 on any of those sheets has any info in it. At any given time, regarding sheet 2 through sheet 150, only about 25-30 of them will have any info in A1. So esentially, what I would like to do is IF anything is entered into cell A1 in any of the sheets inbetween sheet 2 through sheet 150, then display the contents of that respective sheets A1 through A4 onto sheet 1. And for the sheets that don't have any info entered into A1, I don't want any info from that sheet displayed into sheet 1. Can this be done?

Thanks in advance for any and all help.
Jamie

Posted by jrbee on August 22, 0100 5:27 AM

I assumed you wanted the data displayed on Sheet1 with each line representing a different subsequent sheet. If so, in cell A1 on Sheet1 enter:

Sheet2

Using the fill handle, drag cell A1 down thru cell A149. You now have a list of your sheet names in column A (Sheet2 thru Sheet150).

Now in cell B1 enter:

=IF(NOT(ISBLANK(INDIRECT(ADDRESS(COLUMN()-1,1,4,1,$A1)))),INDIRECT(ADDRESS(COLUMN()-1,1,4,1,$A1)),"")

This complicated formula is needed in order to allow you to use the fill feature to copy it to the rest of the cells. So, again using the fill handle drag cell B1 across thru cell E1. Then use the fill handle to drag cells B1-E1 down thru B149-E149.

You should now have what you want. If the A1 cell on any sheet is not blank, the data in cells A1-A4 will be displayed on a line on Sheet1. Each sheet will have a separate line on Sheet1 with its sheet name in column A. If your sheets have names other than Sheet2 thru Sheet150, you will have to put those into column A on Sheet1, but the formulas should still work.

Hope this helps and is not too confusing.

Posted by Thomas Venn on August 21, 0100 2:25 PM

Hi,

If you have data in sheet 2 and sheet 3, then would the data be displayed as follows? sheet 2 data will display in Sheet 1 A1 to A4, sheet 3 Data will display in Sheet 1 A6 to A9? and so on... This question needs to be answered before I can help further? Essentially, how are the results displayed in Sheet 1? 3 to 4 examples would be very helpful.

cheers,

thomas