Coding in multiple sheets

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.