Pulling data from one worksheet to another in Excel

dudara

Here is my problem. I am currently working with a data workbook which holds large amount of info from a manufacturing plant. Various numbers are entered into it at the end of each shift. These go to different locations in different sheets. However, the operators can enter them into wrong locations etc causing problems.

I've been asked if it is possible to create a single form, on a separate worksheet, where the operators can enter the date, shift no and then the pieces of data.

Then these would be automatically assigned to their correct places in the relevant worksheets.

I've been scratching my head at this one, as everytime I think of a possible way, I find some reason why it won't work.

Does anyone have any suggestions?

akari no ryu

"I've been asked if it is possible to create a single form, on a separate worksheet, where the operators can enter the date, shift no and then the pieces of data."
Once more in English?

dudara

This is a large excel file, which has multiple sheets, where operators are expected to enter various figures at the end of every shift. however, data is often mis-entered. These often look like

date weight
14/08/06 905
15/08/06 843

I'm hoping to create, on a new sheet, a form where the operators can enter the data in a regular manner, all in one go, instead of hopping around from sheet to sheet.

Then I want to assign the pieces of data to their correct sheets.

scojones

That shouldn't be too difficult to do with a macro. You can use the following to select a sheet for manipulation:

Sheets("Sheet1").Select

and go from there.

dudara

I was thinking of using something like

=IF(B1=TODAY(),'Input Form'!C5)

B1 is a cell on one of the big worksheets containing a date. if the date matches the date on the input form, then if pulls data from the form.

The problem with this is that it will work fine today, however when I open the spreadsheet tomorrow, the condition is now FALSE, and the data pulled across yesterday is deleted. Is there any way to use this method, but if it evaluates to FALSE, then any previous data in the cell is left untouched?

I don't know how to write macros in VB, where this is probably a simple task.

Tom Dunne

You may not even need a macro.

Correct me if I'm wrong, but is this what you need:

Sheet 1: Data entered in (say) 3 different Cells, A1, B1 and C1.

Sheet 2: Sheet 1, Row 1 data goes here

Sheet 3: Sheet 1, Row 2 data goes here

Sheet 4: Sheet 1, Row 3 data goes here

If that's the case, we will take Sheet 2 as an example. If you want to copy what is in Sheet 1, Cell A1 into Sheet 2 Cell D3, enter the following in Sheet 2, Cell D3
=Sheet1!A1

Then on Sheet 3, cell B19 (say) enter
=Sheet1!A2

and in Sheet 4, Cell D15 enter
=Sheet1!A3

Is that what you need?

dudara

It moves down a row every day.

I can identify the row containing today's date using the MATCH function, so for instance I know that row 674 contains the data for today.

Once I know that, can I write a macro to take the piece of data from 'Input Form'!C5 (for instance) and place it in 'Process Data'!B674?

Ideally the macro uses the date on the input form to search and identify the correct row in which to place the data and then places it.