Dynamically Updated Vlookup Table in Microsoft Excel

I am trying to find a way of dynamically changing the content of a vlookup command. I am producing reports which always look at the last 12 months of data. Each month I need to change all the column headers and all of the vlookup commands. I am trying to find a way to use a vlookup which will look at the column title and use this month as part of the table array name as each months data is stored in a different spreadsheet.

Any ideas or pointers to previous threads??

Hello,

This is my first post on this site. I would like to request some help to code up a macro to do the following: I have two different real-time data feeds (via a DDE / ActiveX) that are going to 2 worksheets within the same book (in the attached 'Calc_Ratio.xlsx' workbook, Sheet1 and Sheet2 respectively). What I need is:
1. Take the last non-blank cell in a given column (Column F in Sheet1 and Column B in Sheet 2) and
2. Do a basic calculation - divide the first value by the second value, which will be updated in the third worksheet (Calc worksheet)
3. Add logic that if the result from 2. (call it 'Ratio') is > a number (0.5), then set adjecent cell next to the 'Ratio' to '1', else set to '0'.
3. Repeat steps 1-3 in close to real-time (say every milli-second).

The hardest thing is to have this code running in a loop and the values in Sheet1 and Sheet2 are dynamically changing (close to real-time).

Please find the attached 'Calc_Ratio.xlsx' workbook. Thank you so much for your advice!

Andrew

Hi!

I am trying to streamline the product overview making it easier to view the sales of three different departments in one overview file. How it works today is that a responsible person in each of the departments adds information to an excel file consisting of columns such as order number, order date, customer, price and so on. It is a day to day update job that adds a unknown number of sales to the overview.

What I want to do is to combine a subset of the sale information added by the three responsible people to a a overview excel sheet that should record these entries and store them meaning that the list till dynamically grow over time. Is this possible? If it is here is a subquestion:
- How do I import an unknown number of rows from three different sheets to one holder sheet without overwriting eachother? All the rows that I want to import are independant of eachother.

An example list would be:
Sheet 1:
Price Order_number Customer
2 k5 IBM

Sheet 2:
Price Order_number Customer Weight
5 l6 Compaq 200

Sheet 3:
Price Order_number Customer
1 m2 Ziggy int.

Combined sheet:
Price Order_number Customer
2 k5 IBM
1 m2 Ziggy int.
5 l6 Compaq

Sorry for the bad example but found it a bit hard to describe it... Thanks for looking into it!

Hi I have an excel worksheet that does the following every 20 minuts : 1.delete all data; 2.connect to a specific site on the internet and get new data; 3.save the new file.
I need the new data after it has been updated to be ADDED to an existing Access table.
my problem is that I can't transfer the data and ADD it to the access table.
does any one have an idea on how to do that ?
p.s.
I think there is an AFTERUPDATE function in excel so if that is true then how do I edit it to write to the access ,because that would solve my problem.
thank you all
udi

A bit more info :
I tryed to add to the excel command(after the data download) a dataset to read the data from the sheet and then with an "INSERT INTO" sql command but it does not recogniaze the needed class for OLEDB optioins(wich is probbly something I did wrong)
I was trying to find an AFTER-UPDATE command in excel but didn't find it.
what I think I need is help with how do I take the new data and add it to the access table
p.s.
the excel file is open at all time and the access file does not have to be - it's all good(open or closed)
thank you in advance
udi

Is there a way to increase the size of the data table dynamically?

Hi All,

I have a dynamically updated spreadsheet (each new row is appended below the last active row in close to real-time - there is a loop that updates code every second), thus very soon the screen is filled and while new data (new rows) are updating, I can not scroll down (basically the macro is working but the screen kind of freezes). What I want is to see only the last 15 rows and hide the rest of the rows (with data) so I have the snapshot of the first and the latest data as it is being updated.

My attempt to do this is below (the first chunk of code is regulating the updates of data i.e. new rows), Test() is working fine so I did not include it here. HideMe() is meant to hide all rows except the last 15 rows. I am only working in a worksheet 'Ratio' the entire time.

VB:

Option Explicit 
Sub Calculator() 
    Dim i As Integer 
    For i = 1 To 4500 
        Application.Wait Now + TimeValue("00:00:01") 
        Call Test 
        Call HideMe 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



VB:

Sub HideMe() 
    Dim ratio As Worksheet 
    Dim last, i As Integer 
    Set ratio = Sheets("Ratio") 
     'Disable Screen Updating
    Application.ScreenUpdating = False 
     'Determine last Row that contains data:
    last = ratio.Range("C" & Rows.Count).End(xlUp).row + 1 'this is the last row, (updates a new row below the last non-zero row) - this part is working 'fine.
     'Hide all rows
    Rows("2:" & last).Hidden = True 
     'Loop through rows
    For i = (last - 15) To lastRw 
         'Hide preceding rows (leave only last 15 rows)
        Rows(i).Hidden = False 
    Next i 
     'Enable Screen Updating
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Thanks much,

Andtherus

All,

I am receiving an error from the Microsoft Query wizard when I try and create a query of an xlsx or xlsm file. The error is " External table is not in the expected format."

Can anyone tell me why this error is coming up and how I can correct the file so that I can write a query to pull info from a Named Range in the file.

I have written some code to duplicate the Microsoft query using ADODB but get the same error message when the Open connection line runs.

I have been using this method with these files for a while now but curiously it has quit working as of late. This is very frustrating as this is a critical method of my workflow.


Thanks for any help you can provide.

John M.

What is the maximum size of a list I may use to create a pivot table?

Hi, may i know is there any setting has to be enabled when i run my 2007 excel file (hello.xls) on 2010 excel?

The macro in this file can run smoothly when i open it with microsoft excel 2007. Yet, when i tried to open my file with microsoft excel 2010, an error msg state that my file is not being supported my microsoft excel 2010.

I have a function Code:

Private Declare Function SetTimer Lib "user32"

in my macro.. i wonder is it becoz of this my file is not being supported.. but i have google that excel 2010 does support 32 and 64.

Please advise... Thank u very much

Today a company e-mailed me a quotation with the extension: xls

I don't have Excel and eventually found Excel Viewer 2003. This took 45 mins
to download, and then didn't work. The Excel File Graphic had appeared atop
the quotation file - but when clicked on Excel Viewer 2003 opened and just
said: cannot open this file type.

Any ideas? Would appreciate solution. Running XP SP2 on a Dimension 8250..

Thanks