Free Microsoft Excel 2013 Quick Reference

Looking up data:pulling information from one sheet

I'm hoping for some help in pulling information from one sheet onto another.

For example, I have no problem setting up a vlookup to find a customer number (happens to be A13) on the other sheet and displaying their name one column over... =VLOOKUP(A2,Input!A1:F20,2)

What I'm unsure of is how to lookup 2 rows below (so that it displays what's in A15) or say 1 row down and 5 columns over (F14). I'm guessing that it may be a vlookup with offset or maybe match?

While the # of columns over and/or rows down from the customer number will always be the same, the rows that the customer info is on will be different... for the A13 example, next week it might be on A16 but I'll still need to lookup one column over, one column down etc. this is why I'm guessing a vlookup or match might be needed.

Thanks in advance!


Post your answer or comment

comments powered by Disqus
Hello!

I'm a newbie and I need help trying to update missing information on a worksheet. I'm trying to learn how to use formulas to pull information from one sheet to another. On sheet "A" I have a sheet containing various incident numbers. Each incident has fields that are missing data. On Sheet "B" I have the identical sheet except the fields have been updated. I want to transfer this information and match it to the matching incident numbers. Please help!!

Hello everyone and sorry for bad spelling, I'll try to keep it as good as possible.

I have an idea that would make my work a lot more simple. The thing is I have an excel workbook that has around 8 sheet's in it. In each sheet are many lines with information about different products. What I do is, I fill in the number of items needed for each product and then copy into one main sheet all the lines from each sheet that I need and send out offers from the main sheet.

What I need is a way for the main sheet to pull information from other sheets if the number of units for a product is not blank. To make this a little harder, I need to get all the information from the line of the product: Regnumber, name, volume, prize, discount, total prize, total prize with VAT.

I'm thinking about using if to do this, but don't think it's a very elegant way

thanks in advanced
-Harf

I Am trying to take information from one Sheet where for example cells F8=(blankcell) F10=Break F12=(blankcell) J8=Cleaning J10=waiting and have it automatically copied to another sheet where the cells that are not blank have their contents listed without blanks and without repeating cells. for example
Sheet2
Break
Cleaning
Waiting

So far i tried using huge if statements in the destination cells to try to locate the cells with information in them

=IF(LEN('PPI DATA'!F8)>0,'PPI DATA'!F8,IF(LEN('PPI DATA'!F10)>0,'PPI DATA'!F10,IF(LEN('PPI DATA'!F12)>0,'PPI DATA'!F12,IF(LEN('PPI DATA'!J8)>0,'PPI DATA'!J8,IF(LEN('PPI DATA'!J10)>0,'PPI DATA'!J10,IF(LEN('PPI DATA'!J12)>0,'PPI DATA'!J12,IF(LEN('PPI DATA'!N8)>0,'PPI DATA'!N8,IF(LEN('PPI DATA'!N10)>0,'PPI DATA'!N10,IF(LEN('PPI DATA'!N12)>0,'PPI DATA'!N12,IF(LEN('PPI DATA'!R8)>0,'PPI DATA'!R8,IF(LEN('PPI DATA'!R10)>0,'PPI DATA'!R10,IF(LEN('PPI DATA'!R12)>0,'PPI DATA'!R12,IF(LEN('PPI DATA'!V8)>0,'PPI DATA'!V8,IF(LEN('PPI DATA'!V10)>0,'PPI DATA'!V10,IF(LEN('PPI DATA'!V12)>0,'PPI DATA'!V12,"Working")))))))))))))))

But i can't find a way for the cells to disregard the cells already listed so what i get is
Sheet 2
Break
Break
Break

Also i can only looking through as many cells as i can make if statements for which is 64 but i will need to have it search through up to 600 cells. i'm considering making another sheet so that the information is listed together even with blanks so that maybe if the cells aren't scattered i may be able to use a different function. any suggestions would be greatly appreciated

EggHeadCafe - Software Developer Portal of Choice
Silverlight 2 RC0 Doing Data Part VIII : Using the Threadpool
http://www.eggheadcafe.com/tutorials...0-doing-d.aspx

I am working on 3 different projects at the same time. It's no wonder I
can't think.
Anyway. I would like to pull information from one worksheet to another.
I would like perhaps a template that will pick up the info from what
ever sheet is in front of the template, and insert the info in the
desired cells, in the template. I think a template is what I want. in
short After I do a sort I want the info to rearrange itself on another
sheet so it's in the format the boss wants.
Is there a way to write a formula so it picks up the preceding page
info no matter what it's name is? Suggestions?

--
CBrausa
------------------------------------------------------------------------
CBrausa's Profile: http://www.excelforum.com/member.php...o&userid=24677
View this thread: http://www.excelforum.com/showthread...hreadid=520635

I am working on 3 different projects at the same time. It's no wonder I can't think.
Anyway. I would like to pull information from one worksheet to another. I would like perhaps a template that will pick up the info from what ever sheet is in front of the template, and insert the info in the desired cells, in the template. I think a template is what I want. in short After I do a sort I want the info to rearrange itself on another sheet so it's in the format the boss wants.
Is there a way to write a formula so it picks up the preceding page info no matter what it's name is? Suggestions?

Hello wonderful excel helpers!

I am stumped on how to get information from one sheet "Status Report" in which the Order Date is located in Column I to transfer to another sheet "Status by Date". I need all of the information in each column A4:14 all the way down to A41:I41 and I need them to be sorted by Order Date. I have a feeling I need to come up with some sort of Macro but am not completely sure.
I have attached the document.

I appreciate any assistance!

Thank you kindly!

Someone please help. I have a formula on my "Inventory List" sheet that pulls info from another sheet. the problem is i dont know what this name of that sheet will be. so i have set it up that the sheet in question regarding its name is dim'd as "thissheet". so how do i utilize this when trying to get the info from "thissheet". as you can see below i just named "sheetsheet" as "Needs Evaluated - Was Aborted". this will work but it causes other problems so it would be best to get rid of the intermediate name and use the "thissheet" designation.

Dim ThisSheet As Worksheet
Set ThisSheet = ActiveSheet

ThisSheet.Name = "Needs Evaluated - Was Aborted"
Sheets("INVENTORY LIST").Select
Range("K2:K3").Select
ActiveCell.FormulaR1C1 = "='Needs Evaluated - Was Aborted'!RC[-6]"

and help would be greatly appreciated.

thanks

Hi Folks,

I have been working on a sheet for monthly graphs. I have managed to create the code getting the spreadsheet to look how i require and get all the information onto it.

However I need some help creating a code to do the following which is extracting the information from one sheet and placing it in the right location onto a different sheet.

Here is the layout i have:

I have two worksheets "Monthly Report" and "Monthly Graphs".

In column "O3" of worksheet "Monthly Report" is the month i am working on (this month is placed there earlier on by code).

In column "P3" of worksheet "Monthly Report"is the figures for this month.

What i want to do is transfer those figures to another worksheet "Monthly Graphs".
So i need a code to do the following:

If column "O3" of worksheet "Monthly Report" says September.
Then i want to go to row "B2" in worksheet "Monthly Graphs"and find/match the column which has September in it.
Once it has found that column i want to place the figures from column "P3" of worksheet "Monthly Report" into row 4 of the matched column in worksheet "Monthly Graphs".
Therefore i have transferred septembers figures from worksheets "Monthly Report" to "Monthly Graphs".

I hope this makes sense but if anyone needs more information then let me know.

I would really appreciate some help to do this.

Thanks

Mike

Hi, I'm new to this forum. I need help with how I can merge similar data from one sheet to another sheet and count the occurrence too? Here's an example;
Sheet 1
Xxxxx
Xxxxx
Yyyyyy
Zzzzzz
Yyyyyy
Xxxxxx

The output on next sheet would be:
Xxxxx appeared 3times
Yyyyyy appeared 2 times
Zzzzzz appeared 1times

Suggestions? Thanks!!

Q: I have a workbook that has 2 sheets.
1st sheet has 4 colums, Customer,Serial,Address and Phone.

2nd sheet is a work order and I am trying to get the information from one sheet to populate into the other by using a dropdown.

So, when I choose a customer name, the address,serial and phone is filled out without having to always keep adding each cell one at a time.

I took a "snapshot" of a demo to help me with my question.

http://www.mbqc.com/excel

I have purchased excel bible, but...................... DEEP!

Please emal any help to me at mbqc@totalspeed.net

Again, thanks for any help.

Hi, I'm a new user to Excel and I need to know if there is a way to get information from one sheet into another. I have a sheet with part numbers in one column and their respective prices in the next. I would like to be able to enter the part numbers on another sheet and have the price put in automatically. I really am an IT virgin so I hope it isn't too difficult.

Thanks

I want to take information from one sheet and have it print directly to other sheets so I don't have to copy and paste. Example:
Information Sheet
Name: John Smith
Address: 1234 Main Street

Sheet #1
John Smith in the name cell
1234 Main Street in the address cell
and so on.

Please understand, I am not real sharp at this but I would love to learn how to accomplish this. I write sales contracts and having to repeat, over and over the name and address and so on is time consuming.

Please help if possible.
Thanks,
Gordon

I'm a new excel user, and I have a relatively simple (I hope) question:

I'm trying to get the cell information from another sheet into another sheet in the same workbook - like each sheet has it's individual calculations, and now I want to take those totals and put them on a totals page.

How do I do this?

Thanks!!!!

Hi All, just wondering if someone could help us as we are not getting anywhere. We have a workbook with 3 sheets. We are trying to get information from one sheet to another depending on our selection. I have attached our workbook to make it easier to understand what we are trying to do. In the commissioning sheet I have put a green cell that you can select a switchboard number... from that we would like to grab information from the fire mode sheet depending what switchboard you select and list it in the commissioning sheet.

Once we get that working we would also like it to put a boarder around the items that get displayed in the commissioning sheet but not sure if that is possible.

R

CB

Hey Guys -

How would you go about getting information from one sheet (within a workbook of 3-4 sheets) and putting that one line of info onto a summary sheet??
I can link pages, etc but i cant do the above.

www.lincolneather.com/tektips.jpg

on the link above - u can see that 2 packs of sin city have been ordered along with other shirts.. how can we get those tees that have been ordered to appear on a new sheet (summary sheet) of what has been ordered

so that a new sheet would only have what has been ordered on it.

thanks for any advice.

I don't think this may even be possible, but what I am trying to do is pull only certain information from one worksheet to another based on whether there are entries on certain dates for employees.

A sample is attached.

On the first worksheet I have a drop down menu for all employees on the second sheet. (the drop down menu pulls the names from the second sheet)

The second worksheet has all employees in Column A and to the right has all their variances by date. Most dates will be blank as they didn't have a variance.

What I would like is on the first worksheet, is to select their name from the drop down menu and have all the dates that they had variances and the variances show up.

If you take a look at the attached excel file it might explain what I am trying to do better.

Thanks for any help.

There’s probably a relatively uncomplicated solution to this, but I’m new to macros don’t know how to pull all the pieces together. I’m trying to create a file that can be used as a timecard for each individual employee to use for punching in and out in real time, while separately creating a record of their in/out times that only a supervisor with password privileges can edit or clear (to prevent employees from altering their own times). I’ve already set up a “Log” worksheet with buttons to punch in/out with the current time (using“= Evaluate("=NOW()")) in multiple in/out columns on a single row that calculates current hours worked (file attached).

Now I want to have these “live” dates and times also logged statically on a second (password-protected) “Log” sheet in the same file. What I have in mind is a row for each day of the month on the “Log” sheet that compares the day of the month in “Log” column D with the date in “Punch” cell E8 and enters the dates and times for that day in the corresponding columns of the “Results” sheet, resulting in a static list of dates and times in and out.

The supervisor would save a copy of the “Results” sheet on a weekly or bi-weekly basis, then clear the dates/times from the original file so that the user can continue to use it without overwriting any needed data from the previous month.

So in short, I’m looking for something that will paste or enter values from one sheet to the other based on the date throughout the month.

Thanks in advance for any advice!

Hello...

Depending on the choice made in a combo box, I would like to pull data from a specific sheet. Could someone give me a jumping off point on how to...

Pull data from one sheet to another and the formatting as well, if with links to the pages will not work...I would prefer no formulas in the sheet where the data is going to.

Thanks for the head start!

hi all!

i'm really new to vb and excel and i need some help! i have a large table of data in a worksheet i have called "1" and i need a macro to pull data from it, do a quick calculation on that data and then display the results in a table in worksheet "2". could anybody write a macro that does what i need? please? in fact, i need the same thing written twice in 2 macros if possible with a slight change in the second one as we are using 2 types of template to store the data in the first sheet (one horizontally stored and one vertically stored - in 2 different excel files, not the same file).

here's the exact specs:

1 - in worksheet "2", the table for data to be put into is actually more like a "heatmap matrix" type thing - but only the bottom half is used to stop duplicates (as the top and bottom of a matrix are always symmetrical so we just use half!). it starts from B3 and runs all the way DOWN to bottom of sheet, then C4 down to the bottom, D5 to the bottom, E6 to the bottom... etc... staggering down with each new column. it only ends when it hits the far right side of the sheet. quite big!!

2 - in worksheet "1", the table for looking up data is a normal table running horizontally (in rows) with headers in column A, these headers are what needs to be looked up so the whole row belonging to that header can be used. but as mentioned above, we also use a seperate file in which we have the same need but the data in "1" is stored in columns with the headers in row 1 (exact same thing just vertical instead of hortizontal). we really need 2 macros that do the same thing, one for the book with rows in and one for the book with columns. the data is shown in sheet "2" exactly the same in both books, no changes needed there.

3 - the calculation: we need each cell used in the heatmap matrix (sheet "2" as described above, B3 down, C4 down etc..) to look up to row 1 to find its "vertical header", and look left to column A to find its "horizontal header", and then find these two sets of data from sheet "1". when it finds them, i need it to calculate the correlation between them using the CORREL function (or whatever the equivalent is in vb code). all the sets of data will always be the same length. so this correlation value needs to be displayed in the relevant cell in the heatmap matrix on sheet "2", rounded to 2 decimal places.

4 - sorry to repeat myself but just to clarify, we need the same thing written twice as 2 macros, but one to handle data in sheet "1" being stored in rows and one to handle data stored in sheet "1" in columns.

thats it! i don't know if that is hard in vb or not, it certainly is taxing for a newbie like me to do even with formulas. having thought long and hard, it seems there is more than 1 way to do this (eg: run down the headers on sheet "2" first and find the corresponding opposite header and do it that way.. it would have the same result so any way you see best is fine!!!!)

any questions please do reply and will be happy to explain further. i think i covered it all. please comebody please help!! thank you all.

gr

Very basic question but I can't figure it out.

How do I pull information (derived from a formula) from cell A1 in one sheet
to cell A1 in a different sheet (but same database)?

THANKS!!!

I am having some difficulty in finding the correct formula to transfer data from one sheet (where the data is stored in a number of ranges on a row) and a database sheet where the data is stored vertically.

The data input sheet looks like this :
ROW#1/A /Value1/Value2/Value3/Value4 Space ROW#/B /Value1/Value2/Value3/Value4 Space ROW#/ C /Value1/Value2/Value3/Value4
ROW#2/A /Value1/Value2/Value3/Value4 Space ROW#/B /Value1/Value2/Value3/Value4 Space ROW#/ C /Value1/Value2/Value3/Value4
etc
These replicate vertically with the normal Excel incremental row increases, however the -

Database sheet needs to look like this
The data from the data input sheet needs to be transferred to this (Database) sheet which displays the information as follows :
ROW#1 /A /Value1/Value2/Value3/Value4
ROW#1 /B /Value1/Value2/Value3/Value4
ROW#1 /C /Value1/Value2/Value3/Value4
ROW#2 /A /Value1/Value2/Value3/Value4
ROW#2 /B /Value1/Value2/Value3/Value4
ROW#2 /C /Value1/Value2/Value3/Value4

I need to be able just copy and paste additional rows in this Database sheet (with all data A,B and C) without having to manually change the cell values.

Can anyone point me in the right direction??
Happy to post a sample spreadsheet if that will help.

Hi, can anyone help me pull entire rows of data from one worksheet into another sheet based on information from one column? I need to feed all "inactive" people from my master spreadsheet into a separate sheet. Will this require a macro or can I do it with formulas? Thank you!

Hi all, got a crazy thing i am trying to do, basically im trying to pull one bit of information from one work sheet to another. But i need it to pull the right values, so i have two work sheets( master and sheet 2 for explaining purposes) so i need sheet 2 to look up a code in master sheet, and the figure related to that code be placed back into sheet 2 next to the code. Included a picture so visulise what i need i can get info from one sheet to another but this is a bit over my head.
crazy excel.jpg

Thank you to any one who can help

Hello everyone.

I have attached a sample file of the data I need to move. My customers supply me with a 4 mg spreed sheet with hundreds of part numbers and i must move this data into a formated sheet with cut and paste. vlookup does not allow for multi rows of almost identical data lookups, or I have not found a way to make this work yet. Pivot tables would make this job imposable to maintain, can anyone tell me how to look up dynamic data from one sheet to another with a data source sheet that changes every week ?

Thank you!!!


No luck finding an answer? You could always try Google.