Excel spreadsheet - linking worksheets in one-many relations

December 6, 2017 at 12:35:06
Specs: Windows 64, 2.812 GHz / 2047 MB
Is it possible to link worksheets together in a one to many relationship based on row number?

The spreadsheet has basically two tabs, Header and Transactions. One or many Transactions are linked to one Header record. What I am looking for is some way to have the Header (Date, Desc) listed to the left of each transaction row.

An example might make this clearer: Test1.xlsx

Ultimately, in that example, we might even seperate out the transaction worksheet into a number of seperate tabs, one for each 'Account'.

Thanks,


See More: Excel spreadsheet - linking worksheets in one-many relations

Reply ↓  Report •

#1
December 6, 2017 at 17:16:53
It's kind of hard for us (me, at least) to figure out what you are trying to do. I see your example, but I don't know where you started to get to that point.

For example, I have no idea how the text Desc 8 on the Transaction sheet relates to the data on Row 3.

I don't know which sheet is an input sheet and which is an output sheet.

You are going to have to be a lot more detailed in your explanation of what your data looks like and what you are trying to do with it.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
December 7, 2017 at 13:09:40
Ok a bit more detailed description, example worksheets in text below.

As you can see the first two columns of the Header table (Date & Desc) are then repeated in each of the relevant rows in the Transaction table. At the moment these are simply copy and paste linked but need to find a better way of doing this.

Header worksheet:
Date	Description		total	overall	0
		
05/01/2017	Desc 8	
05/01/2017	Desc 7	
05/01/2017	Desc 6	
04/01/2017	Desc 5	
03/01/2017	Desc 4	
03/01/2017	Desc 3	
02/01/2017	Desc 2	
01/01/2017	Desc 1	

Transactions worksheet:
Date			Desc		Sub-desc	line totals
								Acct1Acct2Acct3Acct4Acct5Acct6
									
05/01/2017	Desc 8				0	13		-13			
05/01/2017	Desc 7	sub-desc 7c	0	12	-12				
05/01/2017	Desc 7	sub-desc 7b	0	11	-11				
05/01/2017	Desc 7	sub-desc 7a	0	10	-10				
05/01/2017	Desc 6				0	9				-9	
04/01/2017	Desc 5	sub-desc 5b	-3	8		-11			
04/01/2017	Desc 5	sub-desc 5a	3		7	1	-3		-2
03/01/2017	Desc 4	sub-desc 4c	0			6			-6
03/01/2017	Desc 4	sub-desc 4b	0	5	-5				
03/01/2017	Desc 4	sub-desc 4a	0	4		-2	-2		
03/01/2017	Desc 3				0	3					-3
02/01/2017	Desc 2	sub-desc 2b	1	4		-1		-2	
02/01/2017	Desc 2	sub-desc 2a	-1			-1			
01/01/2017	Desc 1				0	1			-1		



Reply ↓  Report •

#3
December 7, 2017 at 14:44:00
As far as I can tell, the only detail you added was this:

"At the moment these are simply copy and paste linked but need to find a better way of doing this."

Other than that, the data looks just like the spreadsheet that you provided earlier.

I assume (which I hate to do) that what you are saying is that you are currently copying the Date and Description from the header worksheet to Columns A & B in the Transactions worksheet and you would like to find a way to automate that process.

So, assuming that I have that right, I still don't know how Desc 8 (or 6 or 3 or 1) is related to the data in the associated row. Apparently, you know why Desc 8 (and 6 and 3 and 1) gets pasted where it gets pasted, but you haven't explained how Excel will know where a specific description should end up. There must be some reason why a specific Description gets placed where it does and we need to tell Excel what that reason is. Therefore, you need to tell us that reason so we can see if we can help.

For 7 (and 5 and 4 and 2) I can see some relationship between the Description and Sub-Descriptions (e.g. sub-desc 7c contains a 7) so for situations like that I could probably write a formula or macro to place the appropriate Description in Column B, unless of course your actual Sub-Descriptions have no obvious "match" to the description or part thereof.

If you want us to help you "find a better way of doing this" then you need to provide enough information so that we understand your process/workflow. Why was Desc 8 pasted where it was? How does it relate to the data in that row and is that a relationship that Excel can automatically determine? Excel is not yet able to read minds (and neither are we).

If Desc 8 (or any other Description) get pasted into a specific location simply because you know that it belongs there but there is no way of telling Excel the reason that it goes there, then it can't be automated. If there is such a relationship, such as a specific piece of data in some other column, then tell us that relationship and we'll try to help you figure out how to pass that information over to Excel.

For example "If a row contains the word Red in Column M, then Description 8 goes in Column B".

Without details such as that, there is nothing that we can offer.

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
December 8, 2017 at 03:35:51
The text 'Desc {n}' and 'sub-desc {na}' are just examples to show the one-many relationship between the records in Header and Transaction. In reality these will be actual real descriptions of items, there is no relevance to the actual text contained within these fields.

Each record (row) in header will have at least one, for some records possibly multiple, rows in Transaction. At the moment I am implementing this by copying and paste linked the date & desc cells from header into transaction. This is effectively providing the one-many relationship required but seems a very primitive way of implementing that relationship.

Is there not a better way of providing that one-many relationship between the header and transactions tables?


Reply ↓  Report •

#5
December 8, 2017 at 04:07:49
Obviously, my long winded request for more details didn't help because you just keep asking the same question. "Is there a better way?"

Do you understand what I am asking you to provide?

message edited by DerbyDad03


Reply ↓  Report •

#6
December 9, 2017 at 06:11:54
Which bit do you not understand?

The actual descriptions used in the example 'Desc 1', 'sub-desc 1a' etc are just placeholders to show the layout of the data and to describe the relationship between header and transaction tables. In reality these will be proper descriptions.

Let me phrased it differently, to see if that is easier for you to understand:
Can records in a transaction table (worksheet) be related in some way to records in a header table (worksheet) using row number? Such that when a record is selected in the transaction table then the header is filtered so that only the appropriate record is displayed?


Reply ↓  Report •

#7
December 9, 2017 at 15:01:48
The part that you don't seem to understand is that we are sitting out here on internet not knowing your process or full requirements. It's all nice and clear in your head, but all we see a bunch of text in a couple of tables.

In addition, you have now added a new requirement: Filtering via record selection. I don't recall any mention of that before. In your other posts all you wanted to do was find a better way than "cut and paste" to place some headers next to some data.

That is why I kept asking you tell us how the headers are associated with the transactions. Once we knew what that relationship is we might have been able to suggest a "better way" than cut and paste.

VLOOKUP perhaps, INDEX(MATCH(...)) maybe. For all we know, a fairly complicated macro might be required. All I can say at this point is that I don't feel that I have enough information to offer any help other than this: If you want something to happen based on the Row that contains the selected cell, then I'm pretty sure a macro will be required. If a macro is required, then we are going to need some very, very specific information be we can even begin to write that code.

Bottom line is that I still don't know what you trying to accomplish, especially now that you've added this "filtering via record selection" requirement. Excel has both basic Filters and Advance Filters. Maybe those will work. Have you looked into them?

Perhaps someone else will step in and either offer a solution or ask the proper questions so that your requirements become clear. As of now, I've got nothing else to offer.

message edited by DerbyDad03


Reply ↓  Report •

#8
December 10, 2017 at 09:53:38
Ok let me try again, this time from a functional point of view.

Overall objective:
What is the best way to use Excel as a database to store records in a one-to-many relationship.

How are Headers related to Transactions:
Not by anything we currently have in data. Imagine the transaction table has a HeaderId column which relates to the row number in the Header table.

Input of data:
Firstly we will input the transaction Date and Description in the Header table.
Secondly we input one or more records in the Transaction table, with each record effectively repeating the date & desc from the header table as the first two columns.

What I don't know is whether there is some clever way of creating one-many relationships between worksheets based on row number? If not perhaps the next best option might be to find a way to automate the copy / paste linked of the date & desc fields everytime a row is inserted into the transaction tab.

Output:
We will want to see all records in the transaction table, along with the relevant dates & descriptions from the header table, where a particular column, say Account 2 value is not blank. I was thinking about best way to input and store the data for the moment as how to obtain the output will differ depending on how that one-many relationship gets implemented.
of Transactions, each related to a Transaction Header. This is primarily about input for the moment, will worry about output later.

Does this make it any more understandable.


Reply ↓  Report •

#9
December 10, 2017 at 11:47:38
Thinking about it further maybe this is one way of implementing it:

In the Header worksheet select the appropriate row, probably the most recent entry.
In the Transactions worksheet display the header current row number, and probably current row's Date & Desc.
Then when inserting a new line into Transaction automatically add a Header.Row field containing the row number of the currently selected row in header.


Reply ↓  Report •

#10
December 10, 2017 at 14:36:35
re: How are Headers related to Transactions:
Not by anything we currently have in data. Imagine the transaction table has a HeaderId column which relates to the row number in the Header table.

Now I have something to work with.

Let's say you know the row number of the "Date and Description" from the Header sheet for a given transaction at the time you are entering the data on the transaction sheet. Let's say you want to use Column C for the Headerid (that Row number you mentioned earlier.)

The following macro will "copy" the values from Column A and B of the Header sheet, based on the number entered in Column C, and "paste" them in Column A and B of the transaction sheet as soon as that number is entered. I put "copy" and "paste" is quotes because the code is not really doing a Copy/Paste, it is actually just setting the Transaction sheet cells equal to the Header sheet cells. It's similar to a "Paste Special - Values" but without the individual steps.

As written, the code is very basic, with a very simple error handling routine. If an invalid number is entered for the row number, such as a negative number or a text string, essentially nothing will happen. That can all be fleshed out with more details as to how user-proof you want to make this. It could even be written such that a drop-down list could allow the user to choose the header text instead of entering the row number. (I say that based on the example data that you posted. I can't say that with 100% certainty without more details.)

Right-click the sheet tab for the Transaction sheet and chose View Code. Paste the following code into the window that opens. Then enter a number in Column C that corresponds to a Header in the Header sheet. The values you want should appear in Columns A & B.

Private Sub Worksheet_Change(ByVal Target As Range)
'Handle bad input (negative number, etc.)
On Error GoTo Done

'Determine if a number was entered into Column C
   If Target.Column = 3 Then
'If Yes, get values from Header sheet
       Application.EnableEvents = False
         Cells(Target.Row, 1) = Sheets("Header").Cells(Target, 1)
         Cells(Target.Row, 2) = Sheets("Header").Cells(Target, 2)
      End If
   
Done:
       Application.EnableEvents = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#11
December 13, 2017 at 06:08:48
Is it possible to obtain the active row from the header table and have that displayed on the transaction table?

Have tried:
=Header!Activecell.Row
in a cell in the transaction table but that just shows error '#Name?'


Reply ↓  Report •

#12
December 13, 2017 at 08:04:41
re: "Is it possible to obtain the active row from the header table and have that displayed on the transaction table?"

I'm not sure what you mean by "obtain the active row". Do you mean obtain the number of active row or obtain the contents of the active row?

You are getting a #NAME error because Excel is looking for a Named Range with the name Activecell.Row. I am assuming (dangerous) that you have not created a Named Range with that name.

Those "dot" style references are a VBA syntax. Excel does not use them in the same manner. It's just text to Excel. See here for more info on the VBA dot notation if you are interested:

http://www.homeandlearn.org/excel_d...

Excel will simply see the dot as text. If you were to actually create a single cell Named Range with the name Activecell.Row, then =Header!Activecell.Row would return the contents of that Named Range. If the Named Range contains multiple cells, you might get the contents of a single cell within that range or you might get a #VALUE error. I don't think we need to get into the reasons for that at this point.

Bottom line is that Excel can not reference the ActiveCell via a formula. Only VBA can reference the ActiveCell. In addition, there is no ActiveCell on a sheet that isn't Active, so even VBA can't reference the ActiveCell on a non-active sheet without temporarily activating the sheet first.

Sub ActiveCellOnHeader()
  Application.ScreenUpdating = False
    Sheets("Header").Activate
      Where = ActiveCell.Address
    Sheets("Transactions").Activate
      MsgBox "The selected cell on Header is " & Where
  Application.ScreenUpdating = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#13
December 13, 2017 at 13:22:59
Perhaps I should re-phrase my previous response to be more clear. When I said "Excel can not reference the ActiveCell via a formula" I meant that "Excel can not reference the ActiveCell via any built in function"

You could write a User Defined Function (UDF) in VBA and then use that UDF in a worksheet cell. In that case, you could use a cell-based formula (the UDF) in a cell and reference the ActiveCell in the Headers sheet, just not in the manner you tried.

Since I'm not really sure what you are trying to do with the formula you tried, I can't offer any suggestions at this point.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#14
December 14, 2017 at 05:13:46
Well the first thing is to have a couple of cells at the top of Transaction displaying Header.Date & Header.Desc for the currently selected Row in Header tab.

Then perhaps when a Transaction row is inserted it could insert (paste link) Header.Date & Header.Desc into the first two columns of that newly inserted record?


Reply ↓  Report •

Ask Question