link dynamic data to cell?

Im not sure if this is the right topic for this question, I apologize if its not.

I have 3 worksheets in my excel workbook. I enter the same data in 2 of the worksheet by means of a bar code reader. However, as the data is entered into sheet 1, the same data has to be entered into sheet 3, but each time the data changes in sheet 1 I need it to move to another row in sheet 3. Im not sure if this is clear so I will try to make an example here:

Sheet 1
Column A

|Product A|
|Order number 123|
|Customer name Bob Smith|

Sheet 3
Row 35

|Product A |Order number 123 |Customer name Bob Smith|

The data changes in sheet 1 every time I scan the item, so I need something that will take the data from sheet 1 column A and copy it to a new row on sheet 3.

Does this make sense to anyone? Can it be done?

Any help would be appreciated.

*Using an = function wont work because the data changes, and it would change all the data in sheet 3, not just row i need updated.

Hello Theguyfromnj,

Need some clarification on your data. Will the data in column "A" always be entered into the same rows or will new data entered in column "A" simply go down the column?

Thanks,
Leith Ross

Originally Posted by Leith Ross

Hello Theguyfromnj,

Need some clarification on your data. Will the data in column "A" always be entered into the same rows or will new data entered in column "A" simply go down the column?

Thanks,
Leith Ross

Each time the data is entered into the same column same rows on sheet "A". The data is entered into row 4,5, and 6 of column A.

Hello Theguyfromnj,

Thank you for the answer. Now that I know what you need I can write the proper macro for you. I won't be able to write it till this evening. Hope that's not to late for you.

Sincerely,
Leith Ross

Can someone help me with this? I thought someone was going to help me with a macro, but I guess they didn't find the time.

Someone help?

JK

Personally, I try to keep all my data on one sheet. I'd use data|filter or
data|sort to get views into how the data is categorized.

If I have to put stuff on other sheets, I'll do all my data entry (whatever
means necessary), then split the data whenever I need to. It's not automatic
during data entry, but it can be mechanized.

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Theguyfromnj wrote:
>
> Im not sure if this is the right topic for this question, I apologize if
> its not.
>
> I have 3 worksheets in my excel workbook. I enter the same data in 2
> of the worksheet by means of a bar code reader. However, as the data
> is entered into sheet 1, the same data has to be entered into sheet 3,
> but each time the data changes in sheet 1 I need it to move to another
> row in sheet 3. Im not sure if this is clear so I will try to make an
> example here:
>
> Sheet 1
> Column A
>
> |Product A|
> |Order number 123|
> |Customer name Bob Smith|
>
> Sheet 3
> Row 35
>
> |Product A |Order number 123 |Customer name Bob Smith|
>
> The data changes in sheet 1 every time I scan the item, so I need
> something that will take the data from sheet 1 column A and copy it to
> a new row on sheet 3.
>
> Does this make sense to anyone? Can it be done?
>
> Any help would be appreciated.
>
> *Using an = function wont work because the data changes, and it would
> change all the data in sheet 3, not just row i need updated.
>
> --
> Theguyfromnj
> ------------------------------------------------------------------------
> Theguyfromnj's Profile: http://www.excelforum.com/member.php...o&userid=35700
> View this thread: http://www.excelforum.com/showthread...hreadid=554849

--

Dave Peterson

Thank you for your reply, but this shreadsheet is used by many in the office, and it is sent to corp at the end of each day, so I can not change the format. All I am looking to do is be able to input the data once and have the data go to the different cells on the different sheets. On the summary sheet, each entry has to advance to the next un-used row, so as not to overwrite the data that already exists.

The perfect solution to me would be:

A single form that can be filled out, and have the data copy to the different specified cells, and when being copied to the summary sheet automaticaly advance to the next unused row and paste the data.

I am not sure if all this can be done. I am sure the form would be easy enough, but the summary sheet is my biggest problem. How do you tell excel to paste data to the next row that doesnt contain data?

You can use code like this to find the next available row.

dim NextRow as long
with worksheets("someworksheetnamehere")
Nextrow = .cells(.rows.count,"A").end(xlup).row + 1

.cells(nextrow,"A").value = "whatevergoes here"
.cells(nextrow,"B").value = "something here, too"
end with

If you're going to make this automatic, remember that you'll probably need
something that deletes data from the worksheet after a typing mistake, and
changes data in existing rows.

I've just found it much simpler to regenerate those separate worksheets after
I've done all my data entry and verified my input.

Good luck,


Theguyfromnj wrote:
>
> Thank you for your reply, but this shreadsheet is used by many in the
> office, and it is sent to corp at the end of each day, so I can not
> change the format. All I am looking to do is be able to input the data
> once and have the data go to the different cells on the different
> sheets. On the summary sheet, each entry has to advance to the next
> un-used row, so as not to overwrite the data that already exists.
>
> The perfect solution to me would be:
>
> A single form that can be filled out, and have the data copy to the
> different specified cells, and when being copied to the summary sheet
> automaticaly advance to the next unused row and paste the data.
>
> I am not sure if all this can be done. I am sure the form would be
> easy enough, but the summary sheet is my biggest problem. How do you
> tell excel to paste data to the next row that doesnt contain data?
>
> --
> Theguyfromnj
> ------------------------------------------------------------------------
> Theguyfromnj's Profile: http://www.excelforum.com/member.php...o&userid=35700
> View this thread: http://www.excelforum.com/showthread...hreadid=554849

--

Dave Peterson

this is good info, thank you... can i get you to be a little more specific with:

.cells(nextrow,"A").value = "whatevergoes here"
.cells(nextrow,"B").value = "something here, too"
end with

whatever goes here? whats that mean? can this actually call data from a cell on a certain worksheet like:

value = "worksheet 3, B:4" ?

It cant be that easy.

thanks again.

worksheets("someworksheetnamehere").cells(nextrow,"A").value _
= worksheets("sheet3").range("B4").value

is one way.



Theguyfromnj wrote:
>
> this is good info, thank you... can i get you to be a little more
> specific with:
>
> cells(nextrow,"A").value = "whatevergoes here"
> cells(nextrow,"B").value = "something here, too"
> end with
>
> whatever goes here? whats that mean? can this actually call data from
> a cell on a certain worksheet like:
>
> value = "worksheet 3, B:4" ?
>
> It cant be that easy.
>
> thanks again.
>
> --
> Theguyfromnj
> ------------------------------------------------------------------------
> Theguyfromnj's Profile: http://www.excelforum.com/member.php...o&userid=35700
> View this thread: http://www.excelforum.com/showthread...hreadid=554849

--

Dave Peterson

I dont understand any of this. You might as well type this in a different language.

Im not sure where to go from here. I have not given up, but I am stopped at present and have no clue where to go from here.


I really do appreciate all the info supplied so far, but excel is not my thing, so I'm sorry but I dont understand much of it.

JK

This is code from a macro.

If you didn't want to use a macro, then the post doesn't belong in the
..programming newsgroup.

If you did want to use a macro, you'll want to read more about them:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Theguyfromnj wrote:
>
> I dont understand any of this. You might as well type this in a
> different language.
>
> Im not sure where to go from here. I have not given up, but I am
> stopped at present and have no clue where to go from here.
>
> I really do appreciate all the info supplied so far, but excel is not
> my thing, so I'm sorry but I dont understand much of it.
>
> JK
>
> --
> Theguyfromnj
> ------------------------------------------------------------------------
> Theguyfromnj's Profile: http://www.excelforum.com/member.php...o&userid=35700
> View this thread: http://www.excelforum.com/showthread...hreadid=554849

--

Dave Peterson