Automate information between tabs worksheets

[Lukhanyo]

Hi Friends,

I am trying to automate data entry between Tabs on my worksheet. I have 12 tabs representing 12 months, and a page where I capture data based on those months.

Currently I reference a cell (eg) =January!A1 and I have to do this in more that 100 rows on different forms.

Is there a way to automate this process, perhaps using Fillhandles, VBA or other means so that I can get a continuous entry of January to December automatically?

Thank you.

[Pete_UK]

If your data is in different sheets, then you need to have a way to tell Excel which sheet to get the data from. This could just be a list of months in your summary sheet.

Then you could use the INDIRECT function to get the appropriate data from that sheet.

You only give one example of the formula that you are using, so that is not very much to go on. Attach a sample Excel workbook by following the guidelines in the yellow banner at the top of the screen.

Hope this helps.

Pete

[Lukhanyo]

Hi The Excel doc is attached.

The aim is to Insert responses automatically on Tab1 "S-21" based on data that is on the other months.

THank you.

[Pete_UK]

There is no example data in the monthly sheets, so I do not know what you want to bring across to the summary sheet.

Please fill in a few tabs with some made up data and indicate in the summary sheet what you want to achieve.

Hope this helps.

Pete

[Lukhanyo]

I have done so, and have limited the sheet to only 3 months instead of 12.

What do I want to achieve? I want the data on Tab 2 to 5, to autofill on Tab 1. Currently this is very long and tedious process.

You will note Tab 2 to 5 has entry spaces for data, this data is the stored on the Cards in Tab 1, I intend to lock Tab 1 so they are not editable since they only draw data from the months.

Yours kindly,

I appreciate your patience.

[Pete_UK]

You can put this formula in C10 of the summary sheet:

=IFERROR(INDIRECT("'"&$B10&"'!E4"),"")

then copy it down to C21.

You can also copy it across into D10:H10, but as the cell reference is contained within a string, it will not change and will always get the data from E4. You will therefore have to amend the formula in D10 to change the E4 to F4, and do the same for cells E10:H10. In H10 you might want to amend the formula further to this:

=IFERROR(INDIRECT("'"&$B10&"'!j4")&"","")

which will prevent empty remarks showing as zero. You can then copy these formulae down to row 21.

Hope this helps.

Pete

[Pete_UK]

Thanks for the rep. I assume this worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

[Lukhanyo]

It worked thanks a lot!