Forum Discussion
2016 Excel Copy Same Cells from Multiple Sheets onto one Sheet
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
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
114 1 1 2
Did you try using INDIRECT formula? You can get whatever data you need from whatever sheet you want with this formula.
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
