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.




Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 4

Mod Emeritus
Trusted
Lifetime subscriber

Topic # 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)




Visit http://www.thecloud.net.nz for New Zealand based Hosted Exchange, Virtual Servers, Web Hosting, FTP Backup & more.
(1GB free FTP storage, or larger plans from $5.75)
 
 - Setup your own mailserver at home on Ubuntu Server - full step by step howto here.
 - Have you seen this: Nathan "KFC4LIFE" Dunn.


Create new topic
291 posts

Ultimate Geek

Trusted

  Reply # 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

1615 posts

Uber Geek
+1 received by user: 420

Trusted
Subscriber

  Reply # 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

 
 
 
 




Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 4

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 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.




Visit http://www.thecloud.net.nz for New Zealand based Hosted Exchange, Virtual Servers, Web Hosting, FTP Backup & more.
(1GB free FTP storage, or larger plans from $5.75)
 
 - Setup your own mailserver at home on Ubuntu Server - full step by step howto here.
 - Have you seen this: Nathan "KFC4LIFE" Dunn.


291 posts

Ultimate Geek

Trusted

  Reply # 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

1615 posts

Uber Geek
+1 received by user: 420

Trusted
Subscriber

  Reply # 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.

3808 posts

Uber Geek
+1 received by user: 602

Trusted
Subscriber

  Reply # 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.

889 posts

Ultimate Geek
+1 received by user: 45

Trusted

  Reply # 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





 



Hawkes Bay
8477 posts

Uber Geek
+1 received by user: 4

Mod Emeritus
Trusted
Lifetime subscriber

  Reply # 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.




Visit http://www.thecloud.net.nz for New Zealand based Hosted Exchange, Virtual Servers, Web Hosting, FTP Backup & more.
(1GB free FTP storage, or larger plans from $5.75)
 
 - Setup your own mailserver at home on Ubuntu Server - full step by step howto here.
 - Have you seen this: Nathan "KFC4LIFE" Dunn.


Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Intel reimagines data centre storage with new 3D NAND SSDs
Posted 16-Feb-2018 15:21


Ground-breaking business programme begins in Hamilton
Posted 16-Feb-2018 10:18


Government to continue search for first Chief Technology Officer
Posted 12-Feb-2018 20:30


Time to take Apple’s iPad Pro seriously
Posted 12-Feb-2018 16:54


New Fujifilm X-A5 brings selfie features to mirrorless camera
Posted 9-Feb-2018 09:12


D-Link ANZ expands connected smart home with new HD Wi-Fi cameras
Posted 9-Feb-2018 09:01


Dragon Professional for Mac V6: Near perfect dictation
Posted 9-Feb-2018 08:26


OPPO announces R11s with claims to be the picture perfect smartphone
Posted 2-Feb-2018 13:28


Vocus Communications wins a place on the TaaS panel
Posted 26-Jan-2018 15:16


SwipedOn raises $1 million capital
Posted 26-Jan-2018 15:15


Slingshot offers unlimited gigabit fibre for under a ton
Posted 25-Jan-2018 13:51


Spark doubles down on wireless broadband
Posted 24-Jan-2018 15:44


New Zealand's IT industry in 2018 and beyond
Posted 22-Jan-2018 12:50


Introducing your new workplace headache: Gen Z
Posted 22-Jan-2018 12:45


Jucy set to introduce electric campervan fleet
Posted 22-Jan-2018 12:41



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.

Alternatively, you can receive a daily email with Geekzone updates.