01-02-2019 06:21 PM
01-02-2019 06:21 PM
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
01-03-2019 12:01 AM
Did you try using INDIRECT formula? You can get whatever data you need from whatever sheet you want with this formula.
01-03-2019 11:34 AM
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.
01-03-2019 03:44 PM
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
01-03-2019 03:45 PM
Hello, can you send me instructions on how to use INDIRECT formula?
01-03-2019 04:54 PM
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
or, if the sheet name has spaces, you need to wrap it in single quotes, like
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
01-04-2019 02:54 PM
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.
01-05-2019 07:07 PM
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
The cell now has a reference like =Sheet2!A1
Repeat for all other cells.
01-07-2019 07:28 AM
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!
01-08-2019 12:24 PM
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.
by Kenny Tilley on August 31, 2018