Match and Index

  • Thread starter Thread starter Samir
  • Start date Start date
S

Samir

Hi All,

I have a workbook containing many sheets with identical
columns. They take the follwing form:

Sheet 1
Col A Col B
100 5000
101 6000
102 7000

Sheet 2
200 5000
201 6000
202 7000

The numbers in column A are codes and column B are
amounts. I want to put all the information from each sheet
in to a final sheet. I can have all the codes in column A
and update them as they change. However, I want to have
Column B updated automatically once there are changes in
the originating sheet. I have tried Match and Index
functions but do not seem to get the desired answer. Can
anyone help? Thanks in advance.

Regards
Samir
 
Hi All,

I have a workbook containing many sheets with identical
columns. They take the follwing form:

Sheet 1
Col A Col B
100 5000
101 6000
102 7000

Sheet 2
200 5000
201 6000
202 7000

The numbers in column A are codes and column B are
amounts. I want to put all the information from each sheet
in to a final sheet. I can have all the codes in column A
and update them as they change. However, I want to have
Column B updated automatically once there are changes in
the originating sheet. I have tried Match and Index
functions but do not seem to get the desired answer. Can
anyone help? Thanks in advance.
Click to expand...

Try VLOOKUP


--ron
 
Hi Ron,
The VLookup does not work for multiple sheets as
Table_Array. Thanks anyway.

Regards

Samir
 
Why not just use links?

Copy the amounts from each page to the "final" page, and then just
"PasteSpecial" and click on "PasteLink".

This linking can be accomplished "en masse", so even if there's a great many
sheets, a single copy and paste special per sheet for an initial set-up
shouldn't be that difficult.

And also, as you insert new codes ( rows) into the original sheets, the
links will *follow* the "original" cells down the column on the "final"
sheet, allowing you to update the "final" sheet with the new code and new
amount without having to change *anything* else on the "final" sheet.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi Ron,
The VLookup does not work for multiple sheets as
Table_Array. Thanks anyway.

Regards

Samir
 
Samir,

This one comes up frequently. A possible solution is to put all the data in
one sheet, using an additional column to differentiate what was originally
the different sheets. It's a one-time copy/paste operation. Now all kinds
of Excel tools (Autofilter, Pivot tables or Data - Subtotal for summarizing,
database functions, etc.) are available. When the data is spread across
sheets, they're not. You almost invariably have to start writing macros.
 
Back
Top