link dynamic data to cell?

[Theguyfromnj]

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.

 

[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

 

[Theguyfromnj]

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.

 

[Leith Ross]

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

 

[Theguyfromnj]

Hello Theguyfromnj,

Thank you for the answer. Now that I know what you need I can write th
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

Whenever you get it done is fine with me. I appreciate it very much

 

[Theguyfromnj]

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

I sure hopr your not dedication too much time to this, its not
extreemly important, just makes my job much easier.

Thanks again.

JK

 

[Theguyfromnj]

maybe it is a bit late to mention this but....

My workbook is set up to use 3 worksheets...

1st sheet is basic data, cells b3,b4,b5,b6 are used (sales name, sales
number, bol #, carrier)
2nd sheet is another sheet that uses the same exact data in
b19,b20,b22,b23
3rd sheet uses the same data again but in the next unused row for
columns A-F

The first 2 sheets clear as new data is over written, but i need the
3rd sheet to keep a running summary of the data that has been going
into sheet1. Appending the data from column A of sheet 1 to the next
unused row of sheet 3.

I was thinking maybe a form would be better suited here, where as I can
enter the data, have it place itself in sheets 1 and 2, and append a new
row in sheet 3. Does this sound easier to do?

I spoke to my boss about using a macro or form to input the data and he
is looking forward to seeing if it works or not. Now I am kinda in a
hurry for it.

I am posting it here because I know people here use excel in a more
advanced way than I ever will, and know the best way to move data
around with the least amount of keystrokes.

Thanks again for your help in advance.

JK

 

[Theguyfromnj]

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

 

[Dave Peterson]

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]

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?

 

[Dave Peterson]

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]

this is good info, thank you... can i get you to be a little mor
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 fro
a cell on a certain worksheet like:

value = "worksheet 3, B:4" ?

It cant be that easy.

thanks again

 

[Dave Peterson]

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

is one way.

 

[Theguyfromnj]

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

 

[Dave Peterson]

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