Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


tonyhughes

Hawkes Bay
8476 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

#26546 25-Sep-2008 15:04
Send private message

I have Googled this, but Google is littered with too much crud on the subject of joining/merging worksheets.

Lets say I have three worksheets (Sheet1 and Sheet2 and Sheet3).
Sheets 1 and 2 are manually updated with data.

I want sheet 3 to automatically gather the data into itself. It doesnt have to do any error or dupe checking.

Does anyone know how? I seem to be able to do some complex things in Excel, but basic stuff breaks my brain.

I have tried the 'Consolidate function, and selected the ranges on both sheets, but just get no output at all in Sheet3 (which I am initiating the consolidation from).

Sheet1
   a       b       c
1 bob    napier
2 fred    hastings
3 sally   gore

Sheet2
   a       b       c
1 mike  auckland
2 joe     hamiltron
3 ian     opotiki

Sheet3
   a       b       c
1 bob    napier
2 fred    hastings
3 sally   gore
4 mike  auckland
5 joe     hamiltron
6 ian     opotiki


(obviously the above is a tiny amount of false data)







Create new topic
AndrewTD
292 posts

Ultimate Geek

Trusted
Lifetime subscriber

  #167076 25-Sep-2008 15:21
Send private message

Hi,

I just typed a big long reply in here suggesting you use the = function to do this, and enumerated examples.
Then I clicked off the page and lost it!

Instead of me typing it all in again - how about you call me on 027 5528 005, and I can talk you through it.
It's fairly straightforward.




kind regards Andrew TD




mdf

mdf
3513 posts

Uber Geek

Trusted

  #167085 25-Sep-2008 15:51
Send private message

Agree with the previous response.

If the data on sheet 1 and 2 is fixed in terms of number of items (i.e. there is no row 4 to be added later in sheet 1), just use the '=' function. i.e. [thiscell] =Sheet1!A1. Type "=" in the formula bar then just navigate to the sheet and cell you want. Excel should do the rest itself. You can then just autofill this out for the rest of your range.

If you need something a bit more complicated, I have used the 'lookup' function for this sort of thing. I understand that this may have changed for Excel 2007 (but is still provided for "backwards compatibility") and there is probably a new (better? worse?) way of doing it, but I am not all that familiar with Office 07 so not really sure.

Cheers

tonyhughes

Hawkes Bay
8476 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

  #167086 25-Sep-2008 15:53
Send private message

mdf: Agree with the previous response.

If the data on sheet 1 and 2 is fixed in terms of number of items (i.e. there is no row 4 to be added later in sheet 1), just use the '=' function. i.e. [thiscell] =Sheet1!A1. Type "=" in the formula bar then just navigate to the sheet and cell you want. Excel should do the rest itself. You can then just autofill this out for the rest of your range.

If you need something a bit more complicated, I have used the 'lookup' function for this sort of thing. I understand that this may have changed for Excel 2007 (but is still provided for "backwards compatibility") and there is probably a new (better? worse?) way of doing it, but I am not all that familiar with Office 07 so not really sure.

Cheers

A large amount of rows exists in each, and will be updated by (l)users from time to time.









AndrewTD
292 posts

Ultimate Geek

Trusted
Lifetime subscriber

  #167094 25-Sep-2008 16:11
Send private message

Having lots of cells referenced using the "=" function is no problem, and if they are in contiguous blocks, then using the autofil or simple cell range extension makes it very easy to extend the referenced range after just typing in one ow two entries.

I do this sort of thing all the time - I have a sheet for each product type, and various entries for units and revene per month per product. On each sheet I have totals cells.

I then have a "Totals" sheet at the front of the workbook, and refer (using the "=" mechanism) to both the product and month titles, and the asscoiated product revenues / units totals.

So I get high level totals on the Totals sheet, and more detailed totals on each product type sheet.

It takes very little time/effort to make up the Totals sheet - whose individual cell contents are almost all references to cells in the other sheets.

Each Row or columns of cells in the Totals sheet is done simply by filling in the left/top most cell, and then using the cursor, extending (autofilling) the cell across/down.




kind regards Andrew TD


mdf

mdf
3513 posts

Uber Geek

Trusted

  #167108 25-Sep-2008 16:46
Send private message

Agreed again.

I have had a look at the "consolidate" option, and I do not think this will do what you are looking for. This is effectively a shortcut function that lets you select some way of manipulating data - e.g. summing, averaging etc. data across worksheets. So it will turn [manycells] into [onecell].

As I understand it, you want to preserve the source data, just redisplay it in a different format.

I still think the "=" function will be the simplest way of doing this; it should not be affected by the raw amount of data.
The other (more complicated) way is the lookup function, where you can get excel to lookup a data reference and display the info in either the next (vector from memory) or last (array) column.

You could also try a pivot report (menu -> data -> pivottable and/or pivotchart on versions I'm familiar with). This purports to be a wizard, but can be a bit fiddly to get operating right. You would need to play around until you got the info in the state you wanted it.

If there's any more info about what you want/what problems you have with '=', let us know as there is usually some form of workaround.

alasta
6703 posts

Uber Geek

Trusted
Subscriber

  #167110 25-Sep-2008 16:52
Send private message

If the individual lists have a variable number of rows and your users are not particularly Excel-savvy then you might want to think about writing a simple macro to sequentially examine each row in the individual worksheets and add them to the consolidated worksheet.

This is pretty straightforward to do if you have basic programming skills.

TinyTim
1042 posts

Uber Geek

Trusted

  #167321 26-Sep-2008 12:31
Send private message

Tony have you got a solution yet? 

 

Here's an option you can use if the data in the first two sheets is contiguous:

=IF(ROW()<=COUNTA(Sheet1!$A:$A), Sheet1!A1, OFFSET(Sheet2!A1, -COUNTA(Sheet1!$A:$A)+1, 0))

(This formula goes in the top left cell of sheet 3. Assumes data starts in A1 of the first two sheets. It copies the row headings from the first sheet, and the '+1' means it's not copied from the 2nd sheet. Copy this down for each row required and across for each column. You could make it more sophisticated to check for cells beyond both tables.)

 

Here's an option using VBA - not very sophisticated, assumes all data is in contiguous blocks (starting in A1). Also copies row headers from 2nd sheet so can be improved.

 

  Sheets("sheet1").Range("A1").CurrentRegion.Copy Sheets("sheet3").Range("a1")
  Sheets("sheet3").Range("A1").CurrentRegion.End(xlDown).Select
  ActiveCell.Offset(1, 0).Select
  Sheets("sheet2").Range("A1").CurrentRegion.Copy
  Sheets("sheet3").Paste





 

 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
tonyhughes

Hawkes Bay
8476 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

  #167349 26-Sep-2008 13:56
Send private message

Thanks for replies.

I will tackle this on Monday. Marketing tasks have got the better of me today, then I'm off to see a man about a dog this afternoon.







Create new topic





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.