473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Referencing Tabs in Excel without Manually Input

I'm trying to create an Excel spreadsheet that will reference the same cell in different tabs, without having to manually select the data within each tab.

I'm creating a row for the data from each tab and would like the spreadsheet to pull information from those tabs. For example, I would like cell A1 on 'Sheet1' to pull in data from the 'Sheet2' tab cell B4, cell A2 on 'Sheet1' should pull in 'Sheet3' B4 and so on. The cell will be the same in each tab (aka B4).

I have a few ideas but no real solution to this problem. Can someone please help??
Sep 25 '14 #1
2 1748
Better yet, instead of separate tabs...what about separate files? Each file is based on a template so they all have the same layout. Is there a way to input the file location (in a cell or via a macro) and have the data automatically pull from the pre-determined cell locations in those files? The data from each file would be on a separate row in the master worksheet.
Sep 25 '14 #2
twinnyfo
3,653 Expert Mod 2GB
KW,

Conceptually, here is what you should be trying to do:

Post #1:
VBA opens the Excel Spreadsheet
You have a pointer to Cell A1
This Pointer (x) increments by Rows
It serves as a basis for another Pointer indicating the Sheet (x+1)
Designate the Source Sheet (Sheet[x+1])
Update Cell Ax with the value from Sheet[x+1].B4
Move to the next Row/Sheet (x = x + 1)
Some challenges with this: How many sheets do you have? Do you always have the same number of sheets? Do you have a standardized naming convention for these sheets or just standard "Sheetx"?

Post #2:
VBA Opens your Master Workbook (that saves the stuff you want to keep)
VBA looks in your designated directory for .xls[x] files
It opens the first file and looks for the data
It copies the data to the master file
It moves to the next file
Some challenges with this: How many files are there? How do you keep track of which files have been opened already? Do you delete the files afterward? how confident are you that they will all have the same identical format?

Just some things to think about. What you want to do is doable. What experience do you have with VBA automating MS Excel? This is what you will need to use.
Oct 1 '14 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Whoever | last post by:
To create Excel file, you can Add Reference Visual Studio.NET, browse to ... and then select Microsoft.Office.Interop.Excel, etc. It endsup some reference to GUID in project file. You can then...
0
by: viktor9990 | last post by:
I'm using the following class to save data from database to an excel sheet. I wonder how to do so without a popup box showing up (open, save)?Thanks I want to save the exel sheet on the hard drive...
3
by: cs_hart | last post by:
Is it possible to access excel worksheets without starting the excel application? If it makes any difference I'm running vb.net 2003 and excel 97. thanks...charlie
6
by: George Slamowitz | last post by:
Hello all I have a HTML control generated by the following: INPUT TYPE="hidden" NAME="MyAnswer" VALUE="" Is there a way to reference the Value of this control using VB program code??? ...
1
by: steveKC | last post by:
Hi, Anyone know how to extarct data from Oracle to excel without the header using vb.net? Below is my coding: Dim rst As ADODB.Recordset cn = New ADODB.Connection
1
by: gabrielk43 | last post by:
My question is this: I use Flex Builder 2 and actionscript 2. Suppose that I have created a simple alert window like that: var alertWindow:Alert = Alert.Show(message);. What if I want to close...
16
by: nicolenwn | last post by:
I have a user form which has a number of buttons on it. Each button represents a vegetable (Eg, Cucumber, Carrot, Tomato) The purpose of this is to take orders from customers for the vegetable...
0
by: hd95 | last post by:
I am trying to insert to an Excel 12 spreadsheet through VB/ADO .Net 2.0 Here's my connection string Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data...
3
by: James Grant | last post by:
Hi Everyone, Like many others before me I have the often asked question of how to create an 'Access-like' input mask for hastening date data entry. Using C Pearson's much quoted code I've...
0
by: ggats11 | last post by:
Can c# talk to excel without vsto and ifso how do I code it?
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.