Preparing an Excel spreadsheet for linking (need attachments for clarification)

[Grandpa]

I am attempting to link two spreadsheet. Linking is not the issue.
Let's call spreadsheet (A) Lab Measurements. You are required to sample "Boogie Creek" for water quality. You must do this five times per month. It doesn't matter what days you sample as long as you sample 5 times. You will sample for flow, volume, pH, and turbidity. After returning to the laboratory you will calculate to find MGD(million gals/day) and (CFS per/day). All of this information will be logged on a spreadsheet under the date of sampling.

Same scenario for the other 4 days that month.

The second spreadsheet (B) is a monitoring spreadsheet including tons of data which is sent to state agencies for proof of compliance.

The reason for being able to link is of course to eliminate multiple duplicate or triplicate entries of the identical data.

OK, now the tough part. Spreadsheet A needs to link to B, however, A's data is always collected on a different date so there is no method of linking other than some type of array. Arrays are not my strong suit, but maybe with some guidance I can grasp the idea.

Thanks to all who comment. I hope someone has had a similar experience and has a simple fix. I have attached A and B spreadsheets.

[pmalen]

I think I can help, but I am not seeing what data flows between the two worksheets. Can you please clarify the connections. Does the data from A need to end up in B, and if so, in what columns/rows?

Thanks

Paul

[Grandpa]

I think I can help, but I am not seeing what data flows between the two worksheets. Can you please clarify the connections. Does the data from A need to end up in B, and if so, in what columns/rows?

Thanks

Paul

I don't have the links established yet. That part will be fairly simple. My problem is how to link a cell which will be included in data from a definite date. Example: The first group of data in A was collected on April 5 and includes RW4, RW5, RW7, RW8. All of the data was collected in the field and there are some calculation within the spreadsheet. The last 4 columns are the reportable results from that day. This could be easily linkable to Spreadsheet B. I would link the cells on the last 4 columns to their correct placement in B. However I have to link it to the correct date in B.


As far as the flow of data, B is just an example of one of 40 pages (didn't want to upload the whole thing). So for experimental purposes any column can be used.

And just a reminder: the object is to input this data into A and be done with it.

It's confusing so I don't mind questions.

[Grandpa]

errrrrr, no replies. Just no interest or is my explination of what I need poor.

[Grandpa]

Hello, this is the second post for the same issue but hopefully this one will be easier to understand, thanks all.

I have an issue with Excel which I can’t seem to work my way out of.
I have two spreadsheets (say A and B). A contains “Date #1” , “Sample Data and Results”, and “Averages”. The “Averages are linked to spreadsheet B on the same date shown under “Date”.
What I want to do is be able to enter, say tomorrows date, into the “Date #1” cell and have the linked data (Averages) link to spreadsheet B, under the new date.
I can’t figure a way to do this, can you. I really hope so.
Thanks

[pmalen]

I see what you are looking for now. I will make you an example with INDEX and MATCH functions - easy solution. But give me a day or so.
Paul

[Grandpa]

Paul,

This is sincerely appreciated, thank you.

Jim

[pmalen]

Try these two worksheets. If you have any questions, let me know. If this was helpful, please click on the * below the post.
Thanks

Paul

[Grandpa]

Thanks Paul. Excellent, excellent work.

[pmalen]

You are welcome!

[Grandpa]

Paul,

I'm having a bit of a problem tweaking the move/index setup you designed. I've been fighting it for a week and just think I'll need some help. I've attached the two spreadsheets that we have sent back and forth so you can see some of the issues. The main problem is that the "Data Collected" dates (of which there will be 5) will not be the same month to month. Whatever date is placed in (for instance) 10/8/2011 will need to link the last columns of that data RW4-RW5. The following month the date in that cell might be 11/5/2011 and will have to link with the proper date in the Report spreadsheet.
You've been great helping me with this. Thank you again.

[pmalen]

i will take a look at it. Might be a few days.

[pmalen]

I looked at it and it should work. What cells in the worksheet are you changing - can you give me specific locations or highlight them in color?

Thanks