Identical data needed on all tabs, want to add a line across all tabs when added in tab1

Hi All,

So I have a Excel spread sheet with the same data in the first say 5 columns, things like first name, surname, reference number, package number, on every tab.

Each tab deals with separate tasks.

Tab 1 is the master data as such and pulls various info from the other tabs.

The issue I have is that when a new line is added in tab 1 (addition of a new person, or a person is allocated a new package) I need that to be reflected across all tabs in the same row as it appears in the lead tab (Tab.1)

Currently the sheet is populated using the INDIRECT formula to create a mirror image of the first tab on the subsequent tabs - problem arises when a new row is added in the middle of the data set, rather than at the start or end. The data all falls out of sync.

Ultimately my opinion is that the user(s) of this sheet are using excel where they should be using a database tool, but the user(s) are adamant that they want to remain using excel to record and maintain this data......

Can anyone suggest anything?

Ok, so I see how that works differently to the INDIRECT formula.

With INDIRECT they have been able to select the exact column data they wanted to repeat from the master data in the subsequent tabs.

To pad this out a bit, in the subsequent tabs one is a list of invoices recorded against that person/package combination.

So imagining the first five columns are plucked from the master data the users then are manually adding invoice details, i.e. invoice numbers and values to the respective rows.

This was resulting in the issue then when a new person or package was added to the Master data and the INDIRECT formula does its thing the automated and manual data ends up on different rows.

Any ideas how this could be resolved?

Thanks for the formula, but I still have an issue

Thanks for your input

Sample Sheet.xlsxOk here it is

Try again, other has a password

just for info, I have blanked some data out, but hopefully it still represents the issue here.

Master data is the updated/maintained page.

As you will see on other tabs INDIRECT is being used to bring in data from the 'Master' tab.

Users are then adding data to the tabs manually in line with the respective reference number etc.

Issue that arises is when a new person is added or a new package added to the Master sheet the data shifts per the formula (formula doing what it should) but the manually added data stays static and ends up on an incorrect line?

Does that make sense?

Thanks

Sorry, do you mean my explanation doesn't make sense to you?

Or you don't know a resolution?

Thanks

Master data = the maintained/updated data.

Finance Tab = is generally OK and working. INDIRECT is pulling through the columns required and the rest of the columns (not populated by INDIRECT) are formula driven so calculate OK.

Invoice List = Has an issue, Col A-C are populated by INDIRECT, but Col.D onwards are manually populated by a user. The issue arises when a new line is entered into the 'Master Data' tab as INDIRECT works and brings through the new line as it appears in the 'Master Data', thus extending Col A-C, but moving out of line the manually added data in Col D onwards.

Case Mgmt Tab = Same issue as 'Invoice List'

The issues have knock on affects on the other tabs, eg. Finance tab becomes inaccurate as SUMIFS is used and if the ref number has moved down, but the manually entered invoice detail added to 'Invoice List' changes the SUMIFS delivers a different answer.

I'm looking for a solution to this that looks similar to what is presented here, maybe there is a way of anchoring the manual entries to the lines they are posted too, or an alternative formula to INDIRECT to bring the info required from the 'Master Data' to the subsequent tabs???

Hope this better explains.

Thanks

Right, so if I put it like this.

Go to Master Data, add a new person part way up the range/or add a new package to an individual.

Now go to the invoice tab, INDIRECT has brought through the new line and as a result moves down col A-C, but D onwards have remained where they were prior to the addition of the new line in the Master Data.

Do that make it any clearer?

Thanks

Im not saying that the sheet is doing something it shouldn't, more of a this is what we want it to do, what formula do I need to make it do that?

I want to be able to add a populated line across all tabs.

I'm aware how to use Tab grouping to make an amend occur across all tabs.

Have you read my full explanation of this issue/resolve I'm looking for?

Thanks

You may be onto something with the group 'add row' command.

Is there a way to select certain tabs 'named' ones, and be able to add a button that can be pressed to make that selection prior to a user using 'insert rows'? Or even a macro that will encompass both functions? So a user would select the row where they want to insert a row then hit the button which would in turn, select the pre-defined tabs and insert a row at that same position in the tab, but in group so that insert row would appear across all macro selected tabs?

Reason I ask is I need to resolve to be useable by lesser excel skilled users, and this needs to be as simple as possible for them to use.........

Thanks

I think it may help yes, did you see my last post

I have an issue, not sure if Im doing this correctly.
Entered the macro like this:
Sub GroupTabs()
Dim sh As Worksheet
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Sheets(i).Select Replace:=False
End Sub
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Master").Select
End Sub

The top part worked fine until I added the lower bit in.

Sorry VBA/Macro knowledge limited but getting better

Please Help

That's excellent, however I would like it to do a little more.

In the newly inserted row is there a way to copy down the formula form the row above so that the manually inserted data in the Master feeds through all the other tabs.

Does that make sense?

Thanks

Hi there,
I have changed to this but still no formulas are pulling down in the finance tab?

Macro entered as below:

Sub GroupTabs()
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Finance List").Range("A4").End(xlDown).Offset(1).Resize(, 3).Formula = Sheets("Finance List").Range("A4:C4").Formula
Sheets("Master").Select
End Sub

So what do I need to do, will this macro pull down the same formula as is shown directly above it?

Or will it only allow a new row to be added and populated with the formula if its the 2nd row of data, then the 3rd, etc etc?

it works a treat, so to extend this to work across more than just the Finance List, I need to repeat the line, and ensure the number of rows that are needed to be replicated on these tabs is amended.

One thing, do all the columns that I want to be populated by this formula need to be together, i.e. if I want 5 columns of data to be copied down do they need to display in A-E in my tab?

Thanks

Sub GroupTabs()
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Finance List").Range("A4").End(xlDown).Offset(1).Resize(, 8).Formula = Sheets("Finance List").Range("A4:H4").Formula
Sheets("Master").Select
Sheets("Invoice List").Range("A4").End(xlDown).Offset(1).Resize(, 3).Formula = Sheets("Invoice List").Range("A4:C4").Formula
Sheets("Master").Select
Sheets("Case Management List").Range("A4").End(xlDown).Offset(1).Resize(, 11).Formula = Sheets("Case Management List").Range("A4:K4").Formula
Sheets("Master").Select
End Sub

Ok, Thanks

Tried your code, but no formulas pulling down again?

What am I doing wrong?

Sorry my code skills need improvement.

Thanks

Still no formulas pulling down in Finance, Invoice, Case Management

Working great, thanks

Must have been a glitch before.

Many thanks for your help with this.

Thanks