Dragging in values from on table to another...

Hi guys can you help me?



I am struggling with a slow spreadsheet

I use aprox 250 Vlookup formula to drag through information from 
sheet to display in a formatted sheet, this makes re-calc a 'cup of te
and cookies' task....as I am now getting fat off cookies, can yo
explain if VBA would be a lot quicker and how I would go about it?

This is the current state of play:-


Sheet Range Called 'QuestionData' is as follows

45012                 Apples                 Green               
Round
49018                 Oranges              Orange               Round
85221                 Pear                    Greenish           
Oval



and the formatted table is:

Column A         Column B  etc....

Number            Colour                                      
85221               =vlookup(A1,QuestionData,3,FALSE)
45012





(Sorry if the example tables are not clear)


Is there a way (using VBA) 

I can say.....


Lookup A1 in 'Formatted Table', search Question Data and Find Vaule
Copy associated column A to 'Formated Table' B

Move to next values in Formatted table and search Question Data an
Find Vaule
Copy associated column A to next row in 'Formated Table' B

keep looping till row (3000)

end Sub

(It would also need to move to the next value if value does not exis
in the table called 'Question Data' as some values although hav
numbers do not have details........yet, as they are added to th
'Question Data' the script would pick them up the next time it wa
run...)


Sorry 'Bout the psuedo, pig latin type dummy code...but hopefully yo
see what I mean???


All the best

Ni

--
Message posted from http://www.ExcelForum.com

0
2/3/2004 11:49:26 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
120 Views

Similar Articles

[PageSpeed] 30

Reply:

Similar Artilces:

#Value Error
Hi I am getting a #value error. The worksheet is set up horizontally. These are the formulas: At the quarter, I have =If(H33="","Quarterly",D33+F33+H33), and so on at each quarter. At the end, the formula in AB33 is =Sum(D33-E33+F33-G33+H33-I33+J33-K33,etc...) I guess it doesn't like the "quarterly" business. Can anyone give me something I can understand that will work around the problem?? Thanks for you help! -- OneFineDay If desired, send your file to my address below. I will only look if: 1. You send a copy of this...

Table with FIFO Balances
Can anyone tell me which table holds FIFO balances in it? Thanks, Jocelyn Jocelyn, IV00102 has the quantities but I suspect your looking for IV10200 which has the history and the current FIFO layers. More info here: http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-inventory-value-via-sql.html Mark (DynamicAccounting.net) http://www.dynamicaccounting.net On Oct 1, 3:44 pm, Jocelyn <Joce...@discussions.microsoft.com> wrote: > Can anyone tell me which table holds FIFO balances in it? > > Thanks, > Jocelyn ...

Put Results in another cell
OK guys/gals - I know this MUST be simple, but I'm very new to excel. I'm trying to look at one column (A), if the work "Dist" or "Sale" is in the Cell, I would like to fill the next column (B) with "--", otherwise, leave the values alone in column B. =IF(OR(A1="Dist",A1="Sale"),B1="'--", otherwise leave value alone in B1 Thanks for everything! To clarify, I would like my formula to reside in column (B), but there is other info already in that column. If the condition in column (A) is met, then (B) must change, ot...

Pivot Table toolbar
Every time I edit a pivot table (Excel 2000)the toolbar launches itself and I have to then re-anchor it manually - this is very irritating! Is there any way to stop this happening? "GeoffS" <crammond-smith@tiscali.co.uk> wrote in message news:39d201c355a8$a7f788b0$a001280a@phx.gbl... > Every time I edit a pivot table (Excel 2000)the toolbar > launches itself and I have to then re-anchor it manually - > this is very irritating! > > Is there any way to stop this happening? I use Excel97 but it may be the same. You can have the toolbar on all the time, positio...

Drag and drop with CRichEditCtrl
I have an application that makes heavy use of the rich edit control. One of the things I needed to do was to customize text when it is pasted or dropped onto the rich edit control. This wasn't too hard to do: I have an object implementing IRichEditOleCallback attached to the rich edit, and in the QueryAcceptData() method I modify the data as needed before adding it to the control. My problem comes with drag and drop, specifically with the "move" drop operation. Following the MSDN documentation, my QueryAcceptData() method returns S_FALSE to indicate that it's processed the d...

Conversion of Cross-Tab Formatted data to qualify for Pivot Table
It's amazing how one can take pivot table data -- double-click in the data section and it "converts" the PT format to a data-table format (fit for a database environment, say access)... Well any way, I receive files that are in a sort of a crosstab format, which I'd love to be able to get to respond to (the creation) of a Pivot table. Is there a way to make this "conversion"? Thanks in advance for any direction offered.. Do you mean something like this: http://j-walk.com/ss/excel/usertips/tip068.htm from John Walkenbach's site. Jim May wrote: > > It&#...

Drag n' Drop emails to a Java application
Hi, We are developing a Java application where we want to import information received in e-mails. The email client are Outlook 2003 (at least for now). The best way would be to be able to drag the e-mail to the Java app and then access/parse the e-mail in the Java app. I have searched the web but can not find anything useful. Do I have to use COM and/or OLE for doing such operations? Please help me!! Best Regards Joacim ...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

dragging array UDFs
Hi I built an array function and it is working well. However when I dra it to other cel regions I got the message "VALUE". The exact formul and the code is below. Thanks for any help. Eduardo Exact formula: {=samLMR(B5:B20;0;0)} Code: x is a sorted array. Public Function samLMR(x As Variant, Optional a As Double = 0# Optional b As Double = 0#) As Variant Dim xmom() As Double Dim xm() As Double Dim sum(8) As Double Dim R As Integer Dim C As Integer Dim ReturnColumn As Boolean R = Selection.Rows.Count C = Selection.Columns.Count n = x.Count n = n - nfails If R < C Then nm...

My Drag Image Flickers
Hi guys, My drag image flickers, I understand the cause of this (I think) but I don't know how to fix it. I use this to auto-scroll during a drag and drop: // in OnTimer() m_pDragImage->DragShowNolock(FALSE); SendMessage(WM_VSCROLL,MAKEWPARAM(SB_LINEDOWN,0),NULL); m_pDragImage->DragShowNolock(TRUE); So since I'm hiding it, then showing it again, it flickers. Can I fix this ? Thanks, Robert A. ...

Q: hyperlink to value and not cell?
I have an Excel workbook (database) with three sheets. The first sheet is an index with hyperlinks to cells in the other two sheets. The hyperlinks are to alphabetical indexes ex: A 144 Snakes 145 T 146 Truffles 147 Turtles 'A' is the column and the numbers are the row numbers. I noticed that when I deleted a row on the second sheet, that the hyperlinks below that row stayed fixed to the cell to which I had assigned then and didn't shift up with the value to which I had intended the cell to link. Using the above example: If I had a hyperlink to A1...

Using INSERT with joined Tables
I need to INSERT an additional address in my "ADDRESS" table for my students. But I need to join the ADDRESS table with my "ACADEMIC" table which has the Year/Term information to meet my criteria. My CRITERIA is: Admit Year must be = "2006" Admit Term must be = "Summer1" Admit Session must be = "01" Address Type must NOT BE EQUAL TO "PERM" I don't want to insert a new record for everyone, only those students who meet the criteria above. My SCRIPT looks like this:- INSERT INTO [Campus6_test].[dbo].academi...

Copy value of field to other form
How do I copy the value of a field from one form to another form that is not part of a subform relationship? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200703/1 hi Matt, mattc66 via AccessMonster.com wrote: > How do I copy the value of a field from one form to another form that is not > part of a subform relationship? You can access any form, if it is loaded, with Forms("OtherForm").Form![FieldName] = Me![FieldName] or Forms("OtherForm").Form![FieldN...

Drag and Drop Emails
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: Exchange Within entourage, when I drag and drop an email from my inbox to another folder, it &quot;copy/pastes&quot; it instead of moving it permanently so it stays in the original location where I was trying to move it from. Seems the only way around this is to right click on the email and select &quot;Move to&quot; and then select a folder (but I have dozens of folders and this is not quick at all). <br><br>Is there a way to change my settings so when I drag and drop emails, it's a &am...

Drag Capabilities #2
Guys, I seem to have lost that little plus sign that appears in the right hand bottom corner of the cell that allows for dragging formatting and content onto other cells. Help in the this would be greatly appreciated. Regards, Subodh Help is available in Excel Help. Try the term "fill handle". -- David Biddulph <subodh.mallya@gmail.com> wrote in message news:02f26450-f3a3-4b84-a5f2-a80c1513dd14@v39g2000pro.googlegroups.com... > Guys, > > I seem to have lost that little plus sign that appears in the right > hand bottom corner of the cell that allows for draggin...

createdon field showing datetime values
Hello there! In Account form I want to add the field createdon but present "datetime" information and not "Date Only" information. In the SQL server I see that there is datetime stored, but in the system customization I cannot find a way to present the time on the form. What can I do? Regards, Elena The time in SQL is the regional time. If your region is +1 hr then the sql time is 23:00. Not the creation time of the account. This feature isn't possible sorry... Regards Ron "Elena" <newsgroup@sieben.gr> wrote in message news:Og6TY38dFHA.132@...

How to show change in value?
I have a sheet with a table of counts of 20-30 events. Each time an event occurs, I increment the corresponding count. At the top of the sheet, I have cells showing various statistics such as sum, mean, std dev. I wold like to be able to show the previous valuesd for these parameters as well as the current value. Is there a way that I can save the current value in a cell before calculating the new value? I could write a macro to do it, but I was wondering if there is a way without writing a macro? Hi Assuming that all of your data is on the sheet, can you not have the same formula, but ba...

drag and drop
I am running Word 07 with Vista. I've lost my "drag and drop" function for moving text in my recent documents. I can select text, but when I click a second time to get the "dragging" arrow, nothing happens and I can't move the text. The option to enable this function under "Advanced" IS checked. I can still cut and paste. I am a freelance editor and have recently installed some templates with macros and add-ins, and I have attached one of them to my normal.dotx for regular use. However, I can't 100% for sure link the problem to that (timi...

Extracting a list of unique values
I have a column of dates in a random order, some dates have multipl entries, some dates are missing altogether. I would like to produce a new column (on another worksheet) of sorte dates that appear (each date only appearing once). I will then be able to create the totals for each date (I can do thi bit) Finally, I would like to add a column of listing all the amounts (as string) for the date in question, so if I have 5 entries for particular date, I get a total of the 5 entries and also a list of th 5 entries -- Message posted from http://www.ExcelForum.com Create a new column, first...

Microsoft Project 2003
How do i change the background colour of a task cell within the gantt chart table -- GDD This is a multi-part message in MIME format. ------=_NextPart_000_006E_01CACCF5.F52EA890 Content-Type: multipart/alternative; boundary="----=_NextPart_001_006F_01CACCF5.F52EA890" ------=_NextPart_001_006F_01CACCF5.F52EA890 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi Gary, Welcome to this Microsoft Project newsgroup :) You can't This facility was introduced in 2007. FAQs, companion products and ot...

Display formulas instead of values
Hi. I'm working on a sheet, and sometimes, after modyfing a formula in a cell, excel (XP) displays me the formula instead of the result.... I have to close, reopen workbnook, or copy-paste form another formula to solve this.... any ideas? I think you place a space before the = sign by accident Excel think it is text now. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Andrew Ofthesong" <Andrew@NoSpam.net> wrote in message news:uO1Ct6dVDHA.652@tk2msftngp13.phx.gbl... > Hi. I'm working on a sheet, and sometimes, after modyfing a for...

Table information going to two subtables
I have a hierarchy of tables that start off with two selections, 1) ingredients and 2) packaging, then it goes into a sub table, 1) ingredients would have a) commodities, b) crop items, c) dairy, 2) packaging would have a) Glass b) Plastic c) Corrugated, etc, then those selections would break down into another sub table that goes more specific. In total there are five tables for five layers. All the ingredient and packaging information is in the same table for the same layer. I have a main form that has two sub forms, 1 sub form for ingredients and the other for packaging, but my problem is th...

How To Show Drag Image while Dragging Item?
I use MFC 4.2. There are 2 CListCtrls in my app. I am implementing a drag and drop function from one list to another. How to show the drag image when I still choose which item that will accept the drop? Thank you. You can use a CImageList and store a drag image. Look at functions like CImageList ::BeginDrag, CImageList::BeginDrag | CImageList::EndDrag | CImageList::DragMove | CImageList::DragLeave. -Seetharam This article may be interesting to you. It's a little older, but it is still useful information: http://www.codeguru.com/cpp/controls/listview/dragdrop...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...