Feb 13 2021 02:40 PM
Feb 13 2021 02:40 PM
Feb 14 2021 05:01 AM
As Mr Rajesh-S informed you, we recommend uploading to Excel file (without sensitive data), no picture please. Describe your project in as much detail as possible on the basis of this file. This would also be a blessing for all of us, as we can understand the problem much better and you can have a Tailor made solution, a win-win situation for everyone.
* Knowing the Excel version and operating system would also be an advantage.
Here is some information and help for the first steps from Microsoft and a workbook with this option.
In Excel there is no simple way of referring to the «previous sheet». But how does it work anyway, e.g. in column B, to automatically transfer the data from column D of the «previous» sheet?
For example, suppose you have 12 or 52 spreadsheets, one for each month or even each week. In column D the monthly or weekly values are added. You now want to prepare the next sheets in such a way that the numbers from column D of the previous sheet appear in each subsequent sheet in column B.
Sounds easy, but it is not. Because a normal reference in the style of "= 'Table1'! D2" would not adapt when copying a sheet or the cells. In Table 2 to Table 12 it would still be called "Table 1". You would have to manually correct the reference in all formulas - in every single sheet!
Another alternative without a macro
The names of the worksheets must match the sheet numbers. This means: The first sheet must have the exact table name 01, the second 02 and so on.
The sheets must also be arranged in the correct order (in ascending order of numbers). So you must not move them, otherwise circular references will arise.
If the requirements are met, you can write the following formula in the first cell on the second sheet, in which the data from the «previous sheet» must be taken over; just adjust the two cell references. The first is the column specification (here column D) in "! D"), the second (here e.g. D2) is the first cell from which Excel should copy the data:
= INDIRECT (TEXT (SHEET () - 1; "00") & "! D" & LINE (D2))
Copy the cells down. If you now copy these formulas into other sheets (with correctly numbered names), cells D2 to D5 will always get the content from B2 to B5 of the previous sheet.
If you can solve your problem with this, I would appreciate a short feedback.
If you do not come to a solution, please inform all of us here as described above if this does not cause you any problems.
Wish you a nice day.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Feb 15 2021 06:33 AM
I can make that work....but I was hoping for the opposite direction. I would be able to input ALL the data on the first sheet and it go to the corresponding sheets once I coded it on the first. If this isn't possible, let me know. You have been a big help either way. Thanks
Feb 15 2021 06:43 AM
This model also doesn't allow them to stay in transaction order.
If I make 10 company transactions and record these all on the primary tab
Those 10 transactions are spread over 3-4 different simultaneous projects and not in any order.
The tabs indicate a different project, but the primary (where i input data) represents the entire company.
I'm researching VBA (no clue) to see if this more what i need. i feel as though I have seen a spreadsheet similar in the past , just cant find currently.
Feb 15 2021 07:17 AM
@Jurac29 I believe you are on the right track when you want to collect all transactional data in one sheet. But I wonder why you would, then, want to split everything into individual sheets. That's certainly not the most effective approach. Excel has several in-built tools that can do the summaries per type for you. No VBA, no difficult formulae. You mention to be "completely new to this". It may be a challenge, but perhaps you want to explore pivot tables, built on the Primary data. I suspect that your real data isn't as simplistic as your example file, so if you could upload something that is closer to your real life situation, that would be helpful. And if you are up for a slightly bigger challenge, you might want to learn about Power Query, or Get & Transform Data as it is called in Excel 2016 and later.
Feb 15 2021 07:49 AM
@Jurac29 The file you uploaded is very similar to what you uploaded before. It it really so basic (Type, Amount, Description) ?
If so, add one more column with, for example, a sequential transaction number. Create a pivot table and then, from that one, you can create one table for every Type with a few clicks. See attached.
Would that work for you?
Feb 15 2021 10:26 PM
@Jurac29 As explained in my previous post, you need to add one more column to the Primary data, a e.g. for unique transaction numbers. I also recommend to transform the data into a structured table. Now you can create a pivot table with the Type codes in the Filter field. You need to learn a bit abut formatting pivot tables to get to this (see picture):
Now, delete the sheet AA, BB and CC as you will let Excel create them for you on the basis of the first pivot table (above).
Now, select a cell anywhere in the pivot table. On the Pivot Table Analyse ribbon
, find the Options button .
Press on the downward pointing arrow on the right hand side of the button and select Show Report Filter Pages... and then OK.
Excel will now create three new pivot tables, each in their own sheet. Try and see if this is good for you.
Start entering new data in the Primary table. It will automatically expand. On the Data ribbon, press Refresh All. All pivot tables will now be updated. Try and see if this is good for you.