Linking cell info one sheet into another same workbook

marvinlowes · Aug 8, 2013

Is there a way to do the following (excel 2007)
here is small example
on master sheet I have 4 columns
a is amount --b is metal—c is wood---d is plastic

Staff is entering data in the rows amount under the “A” column for the amount and again the same figure under the respective category (metal, wood or plastic)
I want each category to have a separate sheet but only want to enter the info once on the
master sheet and have it link or insert that data in the proper category sheet.

If for example my master sheet has 50 various entries for representing the 3 different categories all entered at different times, how do I get that to line up properly on the category sheet without a lot of blank rows. If of the 50 rows with entries on the Master, entries for metel are on random lines, in this example, lines 5, 15, 22 etc, I think the Metal sheet will end up putting them the same way if I have to write some “IF” formula. I want them just to be entered on lines 1,2, and 3 and so on not how it is in the cell on the master.

Any suggestions. I think a database rather than a spreadsheet would better suit this issue but that is not an option. Even entering on the category sheet and linking to the master, but that is not an option due to a privacy issue as well.

Many thanks
Marvin

mumps · Aug 9, 2013

Hi Marvin. By category, do you mean that you want a separate sheet for metal, wood and plastic? I'm also not quite sure what columns in your Mastersheet will have data entered. You enter an amount in column A, for example 10. Then what do you enter in columns B, C and D? Also, will all three columns (B, C and D) have data entered for each amount or will only one of them have data? Could you please clarify with specific examples and provide a little more detail.

marvinlowes · Aug 9, 2013

mumps said:
Hi Marvin. By category, do you mean that you want a separate sheet for metal, wood and plastic? I'm also not quite sure what columns in your Mastersheet will have data entered. You enter an amount in column A, for example 10. Then what do you enter in columns B, C and D? Also, will all three columns (B, C and D) have data entered for each amount or will only one of them have data? Could you please clarify with specific examples and provide a little more detail.

Hope this helps:
Master sheet
amount metal wood plastic
a1 amt column 50 metal column 50
a2 amt column 30 wood column 30
a3 amt column 22 metal column 22
a4 amt column 18 plastic column 18
etc
For every entry under amount an entry goes under ONLY one
of the category column
so my metal sheet should show in this example a1 shows 50 under amount and under
metal.
so everytime I enter an amount, I also enter it under the column it applies to
there are a lot more entries and categories so the posting would be quite intensive
and my problem is once I find out how exactly to “link”

when it comes to, lets say the metal sheet, notice the entries for metal in this example
are on rows a1 and a3 but the master has other entries for the other products on a2 and a4 etc.
I think my metal sheet will end up showing
the a1 entry (50) and the a3 entry (22) but how will it place them on the lines (rows) properly,

If there is a formula in my category sheets something like
“If master a1 cell greater than 0 (zero) then enter in Metal sheet cell” I would have to
know the right IF function for that which I don’t now…I can only spell out what I want.
I would then fill down the column ( on the metal sheet) with the same formula and if nothing is entered for metal
on the master sheet on different rows (which will be the case) then nothing will get entered in the metal sheet.
That part is fine. but for the times when an amount is entered for metal, how do I get it lined up
properly on the metal sheet. It seems because a2 doesn’t refer to metal, same with a4, I am going
to have blank lines in metal. I would like the metal entries on Metal sheet to line up lines, 2,3,4 etc
As you can tell I am at a loss, thanks for responding and your help.

mumps · Aug 9, 2013

Hi Marvin. Place the code below into the worksheet code module (not into a regular module). The code assumes that you have a master sheet named "MasterSheet" and three other sheets named "Metal", "Wood" and "Plastic". If these sheet names don't match the ones you are using, you will have to modify the names to match. Enter the quantity in column A and then a value in one of the other columns. The rows will be copied automatically to the appropriate sheet.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Column = 2 Or Target.Column = 3 Or Target.Column = 3 Then
        Target.EntireRow.Copy Sheets(Cells(1, Target.Column).Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

marvinlowes · Aug 9, 2013

Thanks so much I am going to try the code within an hour or two and will let you know. Marvin

mumps · Aug 9, 2013

Hi Marvin. I just noticed a typo in the code. The second

Code:

Target.Column = 3

should read

Code:

Target.Column = 4

marvinlowes · Aug 9, 2013

mumps said:
Hi Marvin. I just noticed a typo in the code. The second

Code:

Target.Column = 3

should read

Code:

Target.Column = 4

Thanks, it looks like it is going to work, I just gave it a quick try and the entries are showing up on the proper
sheets, I will give it a good test later after I finish up some work. Many thanks..super answer

mumps · Aug 9, 2013

My pleasure.

Linking cell info one sheet into another same workbook

marvinlowes

New Member

Excel Facts

mumps

Well-known Member

marvinlowes

New Member

mumps

Well-known Member

marvinlowes

New Member

mumps

Well-known Member

marvinlowes

New Member

mumps

Well-known Member

Similar threads

Forum statistics

Share this page

Linking cell info one sheet into another same workbook

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock