Coding in multiple sheets

[Jurac29]

I am completely new to this and only have a basic understanding of this program. I am trying to build a sheet that I can attach a code to each individual row. Then if a row is given a specific code “AA or BB” it will copy that row to a specific sheet titled (AA or BB). In other words I want a main sheet that I can code and in turn will build 3-4 other sheets based on the codes given sheet 1....

[#2]

@Jurac29 

Honestly it's a hypothetical question,,, please edit your post & be specific all about your need, better share some sample data & expected output with us!!

[#3]

@Jurac29 

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.

Nikolino

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.

[#4]

@Rajesh_Sinha 

Here is an example of what Im trying to accomplish. when I type transactions on the primary tab, I want it to show up in the corresponding tab. Does this make sense?

[#5]

@Jurac29 

There are more ways to do this, such as with VBA.

Here are just 2 quick examples with out VBA.

Hope I can help you.

Thanks for your time and patience.

Nikolino

I know that I know nothing (Socrates)

[#6]

@NikolinoDE 

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  

[#7]

@NikolinoDE 

This model also doesn't allow them to stay in transaction order.

For instance:

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. 

[#8]

@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.

[#9]

@Riny_van_Eekelen 

[#10]

@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?

[#11]

@Riny_van_Eekelen 

Here is a real scenario. its very simple because i dont need it to do much.

I like the pivot table and I can try using that. can you put the table on a separate sheet?

[#12]

@Jurac29  Also can 1 pivot table cover multiple sheets?

[#13]

@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.

[#14]

@Jurac29 

I've suggested to possible methods, first is VBA macro and another is an array (cse) formula,, check the attached sheet and read the instructions carefully.