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

You could use this

Formula:

=IF(INDEX(Master!$A:$Z,ROW(),COLUMN())="","",INDEX(Master!$A:$Z,ROW(),COLUMN()))

instead of INDIRECT.

You can use that for the "static" master data and then add whatever information you need to the Task sheets.

Regards, TMS

Thanks for the formula, but I still have an issue

I can imagine. Your design is a rod for your own back. Typically, you wouldn't want, or need to repeat rows/columns/cells. It is more usual to have, say, a client (or supplier) master list (worksheet) with a key field and separate transaction worksheets for invoices, payments, stock, whatever. You then enter the transactions in the transaction sheets. You can use the Master List(s) in Data Validation to ensure consistency and use VLOOKUP or INDEX/MATCH to return the detail relating to that Master entry ... though, more often than not, you probably only need one element.

I'll see if anyone else has any ideas but, for me, I'm out.

Regards, TMS

Hi.

I'd be willing to have a go at solving this, though unfortunately not unless an actual workbook was provided containing expected results for different scenarios.

Regards

Well I'm not going to hack in without a password!!

Regards

Thanks. And which are the expected results in here?

Regards

I'm afraid not, no. Apologies.

Perhaps you could give a concrete example involving actual cell references of what could go wrong, and what the result you would instead like to see is.

Regards

I mean that I'm not quite sure of the issue you're describing and would prefer, if possible, to see a case where this issue has occurred, what negative consequences that issue has had on your workbook, and what instead should have occurred.

Regards

Hi
If you want the row to be inserted in all tabs you can group or select all of them before adding a new data in master sheet and then insert a row in master sheet.
So as all tabs are selected the row would be inserted in all the tabs
If you have many tabs then you can use a macro to select all of them

Sub group()
Sheets.Select
End Sub

Hope that helps!!

Thanks.

Originally Posted by seash

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.

This is the part I don't understand, and for which I was hoping you could give a specific example/instruction (e.g. "Go into the Master sheet and do such-and-such. Now go back into the Invoice list tab. Do you see what has happened? It should have been like this...").

Perhaps someone else with a bit more insight will get there before I eventually work out what you mean!

Regards

Hello
Did you try my solution??
@xor lx
Check attached this is what he is having trouble with...

Originally Posted by seash

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

Yes that's easy
Try this macro

Sub GroupTabs()
Dim sh As Worksheet
For i = Sheets("StartTab").Index To Sheets("Endtab").Index
'enter the names of start and end sheets
    Sheets(i).Select Replace:=False
Next i
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Master").Select
'enter the name of master sheet
End Sub

Yes I read the whole explanation
The problem you are facing is that the manually entered data is not shifting with the shift by indirect formula..
So if you would just insert a row with all tabs selected then the manually entered data would also shift down?
Would it help??
Really Sorry if this won't help!!

Forgot to mention
This macro would select all sheets between the starttab and the endtab so you put start tab as master and the end tab as till whatever sheet you want the changes to occur or you can try this macro to select specific sheets

Sub ssheets()
    Worksheets(Array("Sheet2", "Sheet3","Sheet4","Sheet5")).Select
End Sub

Try this

Sub GroupTabs()
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Master").Select
End Sub

Hello
Try this

Sub GroupTabs()
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("Master").Select
Worksheets("Finance List").Range("A4:C4").Formula = Worksheets("Finance List").Range("A5:C5", Selection.End(xlDown)).Formula

Sheets("Master").Select
End Sub

I have done for sheet Finance list
So just type the first row which contains formula to be copied like A4:C4 and it would be pasted form A5:C5 to the end of the data...similarly do for others

Sub GroupTabs()
Worksheets(Array("Master", "Finance List", "Invoice List", "Case Management List")).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Sheets("Master").Select
Worksheets("Finance List").Range("A4:C4").Formula = Worksheets("Finance List").Range("A5:C5", Selection.End(xlDown)).Formula

Sheets("Master").Select

End Sub


Entered as above, selected the 2nd row down in the master list and run the macro, straight away getting an error message stating runtime error '1004'.

Rows are added across all other tabs - OK

No formulas being dragged down though??

Any ideas?

Really sorry my mistake
This one would work for sure

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

Here you can see resize(,3) 3 is the number of columns present like A to C is 3 columns
Hope that helps

Hello
Actually you may have many empty rows in finance sheet as we were trying macros again and again
But practically we would insert only one blank row at a time...so only that would be automatically be filled with the formula...i.e. the first blank row after A4:C4 would be filled with formulas...

You need to do nothing... just put this macro after making the formula copy function for all sheets to your original file which doesn't have any blank rows....
So whenever you select any row on the master sheet and press the button for the macro this macro would automatically insert a row in all the sheets mentioned in the macro and the formula would be copied to the first empty row which is found starting from cell A4...
So each time you press it a new row would be inserted in all sheets and formula would be copied again to the first blank row found after cell A4...that empty row would be obviously the row which is just inserted by the macro ..

Not necessary.
You can have your range anywhere

Sheets("Sheetname").Range("First cell of the formula to be copied").End(xlDown).Offset(1).Resize(,"No. of columns to be coped from the first cell of the formula to be copied").Formula=Sheets("Sheet Name").Range("the whole range of the formula to be copied").Formula

Sample--(to copy B4:E4)

Sheets("Sheet name").Range("B4").End(xlDown).Offset(1).Resize(, 4).Formula = Sheets("Sheet Name").Range("B4:E4").Formula

Also no need to replicate Sheets("Master").Select again and again...just put it once at the end to come back to the master sheet...

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("Invoice List").Range("A4").End(xlDown).Offset(1).Resize(, 3).Formula = Sheets("Invoice List").Range("A4:C4").Formula

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 Check attached
This should work..
Just select any row and press the go button
Tell me if this works...

Hi
Are you kidding me??
I tested it it is working perfectly
I selected row 10 on sheet Master and pressed go button this is the result i got...
Try again it must work...Or maybe there is a demon in your PC..hahahaaa...
Reply me after trying again!!