2016 Excel Copy Same Cells from Multiple Sheets onto one Sheet

Copper Contributor

I have 31 worksheets in one file that I would like to copy the same cell from each day and paste/populate into a column on a separate sheet showing all values from each day.  Can someone please help?

 

FYI I do not have ability to do VBA

 

Thank you

9 Replies

Did you try using INDIRECT formula? You can get whatever data you need from whatever sheet you want with this formula.

Hello,

 

can you describe the scenario in a bit more detail?

 

Is it the same cell on each sheet? Like A1 on each sheet? Is it the same cell each day, or will tomorrow use a different cell? 

 

If it is the same cell each day, you can use a simple reference to the cell. In your target column, select a cell, type a = sign and then click to the sheet and cell you want to copy, then hit Enter. Repeat for all 31 cells on all 31 sheets.

 

If it is a different cell each day, please provide more detail about how you identify the cell to copy. 

i have 8 cells in one row that are all data sets for each day: new admits, re admits, discharges, etc.

 

I would like to get information from each day easily onto one sheet to have all days with information in columns for each category and rows for data on each day.

 

HOPE I AM MAKING SENSE :)

Below is a row that i copied and pasted for one day.  All 8 cells are exactly the same cells each day which are:

Z28           AA28              AB28                AC28           AD28             AE28               AF28              AG28

1141  1  2

Hello, can you send me instructions on how to use INDIRECT formula?

 

Thanks

Hello again, I don't think you need Indirect for this. It should not be used if it can be avoided.

 

With the fixed cell addresses, you can manually set up the references to the cells. The format for this is

 

=SheetName!Z$28

 

or, if the sheet name has spaces, you need to wrap it in single quotes, like

 

='Sheet Name'!Z$28

 

Enter the first formula, then copy it down, adjust the sheet name in each row. Then copy to column to the right and adjust the cell references. Now you can change the data in the original sheets and the formulas will update to show the current content of the cells. 

 

I'm attaching an example for you to inspect. Let me know if that helps

 

 

 

 

Your example looked like exactly what I want to do, but I am not quite sure on how to do it.  is there a way to set formula where it will pull information for each day/column with that formula and get information all at once.

 

Do you happen to have a YT video that shows how to do it?  I learn better by seeing it.

 

Thanks!

I don't have a video for that scenario, but the steps are easy.

 

Click the cell where you want to see the result

type a = sign

click the sheet that has the value you want to see

click the cell that has the value you want to see

Hit Enter.

 

The cell now has a reference like =Sheet2!A1

 

Repeat for all other cells. 

Thank you.  I tried this, but i was only able to do one sheet/day at a time.  is there a way to get all days in a month show up in a column?

 

Thank you for all your help!

Hello, since each target cell points to a different cell on another sheet, and since there does not seem to be a pattern or logical rule to build a formula, you will need to edit each target cell.  In my post above I explained how you can copy and paste the reference and then adjust the sheet name or cell address. That will make the process faster. You only need to set it up once, but there will be some work involved.