Software

Ask something
Discussion Locked

Excel linking to worksheets

0 helpful
7 replies

by sue t

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a workbook with 5 worksheets. One column has a school code entered. I would like to find a way to have the data in a row automatically copy to another worksheet by using the school code which should tell it which worksheet to copy to depending on the code in that cell. Is there a way to do this?

7 replies

by mikex

Yes of course :

in the formula cell enter =IF("cell ref"="school code","expected result if the condition macth","what to do if the condition doesn't macth")

Example : IF(a1=TS,a2,ERR)

so in this case if in the cell a1 there is a string "ts", the formula will bring back the content of the A2, and if not you'll get "ERR"

in any one condition - example IF(Y,Z,B)
instead of Y,Z,B you can put another if with another YZB conditions

by sue t

Sorry for the delay. I am trying to find a way for it to copy the whole row to another worksheet depending on the value in one column. This is only copying the first column and also when it copies it to the worksheet it is copying it to the same row number which is leaving blank rows between records. Is there a way to copy the whole row and have it copy to the next empty row on in the worksheet?

by ajones17

Here's a macro that can do it, assign it to a key combination and press that when you are on a row containing data you want to add to the corresponding worksheet. You should have the school code in column A and the worksheet should have the same name as the school code. Call your main data sheet "Data".

Sub AddData()
' This Macro adds data to the sheet named in column A
' Give your input sheet the name Data or change the word Data in this macro to the sheet name
' Assign the macro to a key and press it when you are on a row to add that row to the corresponding sheet
Dim x As String
x = Selection.End(xlToLeft).Value
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Sheets(x).Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Data").Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, 0).Select
Selection.End(xlToLeft).Select
End Sub

by sue t

Poster rated this answer. I gave this to our Business teacher and he was not able to make it work. He assures me he followed your directions exactly. Thank you for responding to the question.

This discussion is closed to new replies.