Lookup In Excel

[Shin25]

Hi All

I have a workbook with many sheets, I want to be able to copy ranges of data from each worksheet and transfer it to the last worksheet called 'Total'. What syntax do I need to use?

For eg. in sheet one I need to copy range a1 to a3 and also in sheet 1, I need to copy range k2 to l9?

How can I do this?

 

[jbanas]

if you are looking to sum the columns for total you can do the following. =SUM(Sheet1!A1:A3)+SUM(Sheet1!k2:l9). hope that helps

 

[Shin25]

Cheers for the above but Iam not looking to sum the columns just want to extract the range of date from sheet1 and put that in the 'Totals' sheet.

What syntax would I write and where would I place that syntax?

 

[AnotherHiggins]

If I understand you right, you just want a few cells on the total sheet to be the same as a few cells on another sheet.

On the Total sheet in cell A1, press the equal sign (=). Then use your mouse to click on Sheet1, then click on A1. Press <Enter>. That's it. Search Excel's help file or this forum for "Fill Handle" so you don't have to do this with every cell, on every sheet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Shin25]

thanks for the above.....but I need to do is copy a range of date from 'sheet1' to the 'total' sheet.....not a singular but a range....

 

[AnotherHiggins]

Right. Hence the Fill Handle.

Alternatively, assuming that you are dealing with contiguous ranges, you can do this:

- On the Total Sheet, select A1:A3 (or whatever)
- Press equal sign
- Navigate to Sheet 1 as above
- Click on A1
- Press <Ctrl> + <Enter>

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

Are you talking about doing this in a macro ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Shin25]

Brilliant that works....but now for the best bit.....I have 6 sheets and for each sheet I need to copy a range of data to the 'Totals' sheet....I dont want to manually go into the 'Totals' sheet and do all this work I want it to work automatically....is that possible without VBA code? For example, like a lookup up that simply pickups the values once the workbook is opened.

 

[xlbo]

If the values / rows change each time then probably not without VBA...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Shin25]

Hi the cell range will not change...the range will remain the same.....

any ideaS?

 

[AnotherHiggins]

I'm still not clear on what it is you want.

Notice that you have dynamic formulas on your Totals sheet now. If you change Sheet1, A1, that change will be reflected on the Totals sheet.

Initially, you will have to set up the formulas for all 6 sheets. But once they are in place, they remain dynamic and what you see on the Total sheet will change if the info on the 6 feeder sheets changes.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Shin25]

AnotherHiggins......sorry to confuse you...I will test your theory with the live data and see what I get.....

Many Thanks for your help.....

 

[Shin25]

Hi that did not work....the data on sheet1 range a1:a10 is derived after data manipulation in the background and hidden by hiding the columns. The error message coming back is '#VALUE!'.......any ideas?

 

[thegrover]

I had to do the same thing and found this, it works.

http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=136372&d=1032&h=1033&f=1026

Iain Robertson, 3-Feb-2005

VBA to Amalgamate Worksheets
Hi there,
If you select Tools > Macro > Visual Basic Editor and paste this code in, then hit the play button you'll end up with all of your data on one tab.

Notes:

You need to insert a new worksheet at the *front* of the workbook to receive the data.

This sheet should have values in cells B1 & B2 - it doesn't matter what you put in.

Replace n below with the number of worksheets in your workbook.

Caveat:

Assumes you have a header row on each sheet. If you have no headers replace "R2C1" with "R1C1" below.

Sub amalgamate_tabs()


Dim intRowNumber, intNewRowNumber, intSheetNumber As Integer


intSheetNumber = 2

For i = 1 To n 'The number of sheets with data

'Activate the data sheet
ActiveWorkbook.Worksheets(intSheetNumber).Activate

'Goto cell a1
Application.Goto Reference:="R2C1" 'Replace this if you have no header row

'Select all of the data on the sheet and copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

'Move to the receiving worksheet
ActiveWorkbook.Worksheets(1).Activate

'Goto the first data cell in the receiving sheet
Application.Goto Reference:="R1C2"

'Find the number of the last completed row in the receiving sheet
'and assign this to intRowNumber
intRowNumber = Range("b1").End(xlDown).Row

'Increase intRowNumber by 1 to reference the first blank row
intRowNumber = intRowNumber + 1

'Move to the first blank row
Range("b" & intRowNumber).Select

'Paste in the data
ActiveSheet.Paste



'Add in the sheet name to allow tracking back in case of problems
Range("a" & intRowNumber).Select
Range("a" & intRowNumber).Value = ActiveWorkbook.Worksheets(intSheetNumber).Name

'Autofill to populate the sheet name down the range

'Find the new number of rows in the spreadsheet
intNewRowNumber = Range("b2").End(xlDown).Row

'Check that there is more than 1 row to fill
If intRowNumber - intNewRowNumber <> 0 Then

'If there is then autofill the range
Range("a" & intRowNumber).Select
Selection.AutoFill Destination:=Range("a" & intRowNumber & ":" & "a" & intNewRowNumber)

Else

'No need to fill
End If

'Increase the counter to reference the next sheet
intSheetNumber = intSheetNumber + 1

Next i

ActiveWorkbook.Worksheets(1).Name = "All Data"

End Sub

Hope that helps, Iain

 

[SkipVought]

"I have a workbook with many sheets, I want to be able to copy ranges of data from each worksheet and transfer it to the last worksheet called 'Total'."

The reason that you are having so many problems is that you got the cart before the horse. It is a typical mistake that novice spreadsheet users often make -- chopping data up into various sheets, based on some DATA element, like a date or a department or a region or a manager.

THEN...

they want to get the data into soe sort of consolidated form, and that's where it becomes "interesting" ro rather TIME CONSUMING, blood, sweat and tears.

Excels, plethora of data analysis and data reporting features are based on consolidated data in one or more tables, from which one can REPORT by data elements like a date or a department or a region or a manager. Nad those REPORTS can be on many sheets, if one so desires.

So, my humble suggestion would be to spend your efforts getting all your source data together and keeping all your source data together (a ONE TIME effort), and then use Pivot Table Wizards, Filters, Charts, Subtotal Wizards, to report the data as you wish.

Your TOTALS would then be something that, I'd wager, could be done in a mere SECONDS using a PivotTable.


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]