Software

General discussion

Gravatar
0 Votes
Locked

Link Excel record across sheets

By dennisbv ·
I am cursed with an Excell report that was dumpped in my lap. Using anything but Excell is not an option. I have a first QTR report with truck maint, and I have second QTR. I have to do a Year to date. The problem is they have four locations with multiple departments and these trucks get moved between them. What I need to do is to be able bring a trucks information from the Qtr reports to the YTD no matter how the Qtr is sorted. I Have to follow the format of all of the qtrs and ytd in one work book. Can I link my cells by the trucks serial number so no mater what location or dept it is in it will show corectly on my YTD?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by sgt_shultz In reply to Link Excel record across ...

i don't think so if i am following you. you can easily reference all the sheets in this workbook and others in your formulae. see excel help for the syntax...you need a lookup function to find where the serial number is currently living...let me see...

gravatar
Collapse -

by sgt_shultz In reply to

search help for look up. this is from my Excel 2002 help. hope is is of some use...
---
Use the OFFSET and MATCH functions to do this task.

Use this process when your data is in an external data range that you refresh each day. You know the price is in column B, but you don't know how many rows of data the server will return, and first column isn't sorted alphabetically.

Worksheet example
The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.


Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
A B
Product Count
Bananas 38
Oranges 25
Apples 41
Pears 40
Formula Description (Result)
=OFFSET(A1,MATCH("Pears",A2:A5, 0),1) Looks up Pears in column A and returns the value for Pears in column B ( 40).


The formula uses the following arguments.


A1: The upper left cell of the list, also called the starting cell.

MATCH("Pears",A2:A5, 0): The MATCH function determines the row number below the starting cell to find the look up value.

"Pears": The value to find in the lookup column.

A2:A5: The column for the MATCH function to search. Don't include the starting cell in this range. Make sure the range allows for expansion of the list.

1: The number of columns to the right of the starting cell to find the lookup value.

gravatar
Collapse -

by dennisbv In reply to

Poster rated this answer.

gravatar
Collapse -

by a.barnes In reply to Link Excel record across ...

You can use the VLOOKUP function to search for the truck serial number. Use Insert/Function and lookup for VLOOKUP, and then click Help.

gravatar
Collapse -

by dennisbv In reply to

Poster rated this answer.

gravatar
Collapse -

by dennisbv In reply to Link Excel record across ...

This question was closed by the author

Back to Software Forum

Start or search

Related Discussions

Related Forums