I have linked two workbooks together but now need to be able to convert that linked data to straight text. I have a v-lookup in the report that needs this to be this way. I do not want to use paste special values because that would defeat the purpose of linking the two pages.
View 1 RepliesI have a bunch of information in word that I am converting to excel. I have attached the document called test for you to see.
How do I get all the information between the word "focus" to migrate into rows of data so that I can get one company on one row?
For example line one should be like finished product attachment.
I have atext file like this.
< ZEEO:ALL;
LOADING PROGRAM VERSION 22.10-0
BSC3i BSC1000 2012-04-10 11:23:47
BASE STATION CONTROLLER DATA
NUMBER OF PREFERRED CELLS ........................(NPC).... 3
[Code]....
Now when I am running the macro the output is coming for a single text file as input like..
BSC
NPC
GMAC
GMIC
[Code]....
It should be only a single row as output,but here it is coming in multiple rows(as no of columns) the same data .this is the problem.when I run the same macro for multiple text file it is working properly.
When i open my workbook that has links to another workbook it asks to update. When i select yes after a good 5 minutes (dont think it should take that long) it returns N/A#. The formulas are using a =sumif(from other workbook,this workbook,from other workbook). However as soon as I open the other workbook everything automaticaly updates and is fine.
I have a list of names in column A with monthly budget figs next to them in columns B,C,D etc. At the bottom of the list there is a total spend summary on line 7. This spreadsheet is then pasted with links to a separate worksheet (see example)
If an additional name is added to the list in Row 7 in the original list, the total is now in row 8 - how do I ensure that the linked sheetl picks up this addition?
(I have a project where I am drawing data from numerous worksheets. I have paste linked them as tabs to a summary worksheet so that funtions such as SUMIF can update when the file is closed or open)
I have several Workbooks, say BookA, BookB, BookC (or more). Let BookB and BookC be linked to BookA. I don´t know the actual names of the Workbooks though as the user may change those any time. Let BookA have several Worksheets, one named SheetA1. Let BookC have several Worksheets, one named SheetC1. Now when a specific cell in SheetA1 changes, I want to set the value for a specific cell in SheetC1 (using Worksheet_Change for SheetA1). How can I get at the actual name of that cell in SheetC1? I did imagine to loop through all the Worksheets in all linked Workbooks till I find one named SheetC1, but somehow I can´t manage the right syntax for that.
View 6 Replies View RelatedI hope I can explain what I am trying to accomplish properly. Many years ago (5+), I had this exact workbook setup, but am having a lot of difficulty remembering VB & Excel and making this again from scratch.
I am looking to create an Excel Workbook. The workbook will contain numerous tabs, each tab will essentially be a template, and contain basic formulas.
On tab 1(Sheet1), I would like to have a listbox. The individual items in the listbox, should have the same name of, and be linked to all tabs in the workbook.
There will also be three buttons.
Button1 - Generates the names in the listbox based on existing tabs within the workbook
Button2 - Opens a new workbook, and copies over the selected tabs (AKA - creates a working copy of the workbook)
Button3 - Clears the selection and resets the listbox.
The number of tabs, and contents of tabs, within the workbook will be changing. So the listbox should know to look for all tabs, be it 5, or 50.
The end result would allow someone, who knows nothing of Excel, to open this workbook, as a read-only file. Generate a list of available tabs, select the tabs they need, generate a new workbook containing these tabs, that they can then edit to suit their needs.
In my line of work (and I am sure everyones) there is a huge problem with consistancy. This leads to a snowball effect of problems carrying over, and being copied from one project to the next. As I mentioned earler, I once already had this workbook setup, and it solved all our consistancy problems. I am hoping to do the same.
I have an excel program that uses a modeless userform. often times i will open other spreadsheets while the form is active or minimized. if i return to the form and run it without manually bringing focus to the appropriate workbook, i get an error because it references the wrong workbook. is there a way to keep that form tied to that workbook no matter what? also, it is important that this procedure works despite the workbook name changing.
View 5 Replies View RelatedI've run into a roadblock in excel 2003 in trying to create an accounting spreadsheet that will pull in information from several separate workbooks (invoice files) using formulas which substitute invoice Nos. (which coincide with the tab/worksheet names).
My obstacle has something to do with order of operation/calculation I presume... I've successfully used labels to bring in information from separate tabs on the same file by using the substitute function with generic formulas.... in this case exel processes the formula, likely because it calculates the linked information first in its order of calculation; then trys to substitute information into the result of a calculation already made....
I'm trying to find a way to tell excel to look at respective cells in the same file but on a different tabs/sheets...
For example; I've been trying to pull in the date an invoice was issued for client "X".
I plug in a formula in a remote cell of my accounting spreadsheet "Y" which basically says to bring in the value on file ["X"]; worksheet "tab"; cell $J$11 - where "tab is just a filler that I would like to use the substitute function to exchange with respective invoice Nos. (i.e. 049; 050; 051; etc.) My hope is that by adding these 3-digit sheet identifiers; all other information from respective cells (in file "X") will be pulled into my accounting sheet - file "Y" (i.e. invoice total; applicable pst; gst; expenses; etc.) I have written as many formulas as required for the different types of info sought after, the only variable being the 3-digit worksheet identifier. The problem has been that excel first processes the formula in the remote formula cell(in file "Y"); then applies the substitute function to info already brought in from File "X"; whether it is a date value; or a currency value. I can't figure how to have the substitute function change the formula... not the result of the original function...!
I've tried to format the source formula cell as text (therefore not calculating a result at that instant) however the substitute function inherits the format of the source formula cell.
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
The situation is that I have created a workbook in a network folder: xprojectsexcelclientAApril
umbers
The workbook links to a few other workbooks on the same network, but in the parent folder: xprojectsexcelclientAApril
The problem occurs when my colleagues have to updates the links in the main workbook, because they have not mapped the network drive in the same way as I have. The problem is NOT the prefix, but the fact that they have a more direct link to the network folder. My collages have mapped the folder like: xclientAApril
Is there a way where I can make the links in the workbook, so they don't depend on the entire string, but only on the fact that it is in the parent folder?
I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....
The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies View RelatedI would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column
The difficult part is that there is text in column A, and is linked to the numbers
So if numbers are sorted the names should be sorted in the same way as well.
I am trying to return the value (date) of a construction schedule by searching for a specific construction activity ID number. Is there a method I can use which incorporates a text search so that as the schedule grows (cell locations shift down) the lookup function still follows the unique activity ID?
Below is a sample of row of the ID I must search for, and the date I must return (on a separate excel file):
A
B
C
D
-
Activity ID
Description
Start Date
End Date
1
L3S4C10020
Supporting Walls to UPTS Slab 3
19-Jan-14
25-Jan-14
When selecting from a list of text items in a pull down menu in a cell how can you link that change to other cells to effect a change in them. An example would be if the pull down menu was in cell A1 and as a result of the text selected to be shown in A1 resulted in a need for the text in C3 to be changed as well as the formula in cell D3 to be change which will result in a numeric answer in cell D3. Not all of the choices in cell A1 will require that the text and formula be change in cell C3 and D3.
View 3 Replies View RelatedI have a list of football fixtures that I want to pace in date order. The fixtures are however linked to a grid which has the following formula =IF(FIXTURES!C1="","",FIXTURES!C1 )
so that a zero doesn't appear until a result is actually added. Unfortunately when you sort the fixtures into date order the grid still looks at the original square, NOT the moved one appertaining to the fixture.
Has anyone ever seen a problem like this? The Legend text is not displaying what is in the cell reference and I cannot figure out why.
View 9 Replies View RelatedI have a datafeed program that offers multiple DDE link types to retrieve different types of data. After pasting the DDE links into Excel, all works well. However... To modify the links means manually editing upto 12 different array formulas located on different pages then pressing Ctrl + Shift + Enter after each to change the data source being analysed.
I found that I can manually copy and paste a few characters into each linked array formula, Ctrl+Shift+Enter but it needs to be by Macro referring to a couple cells (Say A1, A2) containing the variables that alter the data source being linked to.
I need to make a VBA Macro that refers to these two cells containing text, and modify the 12-odd linked array formulas.
eg. {=SUB33|getlocation!'N,pg,9,vp,A,30'}
In this example Linked Array formula, the bolded PG and 9 would be variables pulled from cell A1 and A2.
All other components of the array formulas stay unmodified, it is only the "pg,9" text that needs to change in each linked array.
I have a button with a macro assigned. The macro needs to create a new worksheet at the end of the workbook, rename the new sheet to "New County", make the cell size match the rest of the workbook, and then paste in a template from another sheet called "Master".
I recorded a macro that does this, but it has a static reference to the new sheet instead of what ever the new sheet is. So the Macro works one time, but never again. I'm sure this is an easy fix, and perhaps there is a much easier way to do this, but my limited VBA knowledge is hampering me.
Code:
Sub AddNewCounty()
'
' AddNewCounty Macro
' Adds a new County sheet to the workbook.
'
'
[Code]...
I have an excel file that contains data imported from a csv file. One of the columns contains text strings that I would like to convert to a different text string. The column that I want to convert has text data that is similar to the following format:
BL BLDO
BLDO
BLDO
BLDO
EL BLDO
Now, the data above should look like this:
+CONC
CONC
CONC
CONC
-CONC
The "+" should replace the "BL" in the first row. The "CONC" should replace the "BLDO" in all rows. The "-" should replace the "EL" in the the bottom row. Within these files that I work with, there are perhaps 60 or 70 unique text items that each have a corresponding text item that should replace it, and the files usually contain upwards of several hundred to one thousand rows of data. In other words, I have a list of 60 or 70 text strings that have a specific replacement text string...and I need to apply that conversion to several files each day.
I am currently trying to find an easy way to convert cell references to number format. The issue I'm having is when compiling a small formula (CONCATENATE) as part of a macro in order to produce a unique reference number to a column of data (1000's of cells long) I come accross the age old number stored as text issue after the macro has converted the formula to values.
Have tried editing the macro to number stored as text = false which removes the annoying little red triangle but the vlookup functions still wont read the values. My current solution is to highlight the entirity of the data and convert to number which takes a long long time as there are multiple lookups assigned to the cells, which for my current project is not acceptable.
Am looking for a quick solution, (with no interaction required) something that would convert the cells to the format required either before the formula is assigned and overwritten by values or just after. Currently I'm stumped.
I need to convert my percentage marks into values based on competency values. Once again my school board has change the final reporting method. Last year we had to use A, B, C, D. This year it is based on scales of 1- to 5. Since excel cannot have more than 7 nested IF statements I was wondering if anyone knew of a way to have a percentage mark converted into a scale number. Below is the following data.
0-5% = 1-
06-15% = 1
16-25% = 1+
26-35% = 2-
36-45% = 2
46-55% = 2+
56-59% = 3-
60-64% = 3
65-70% = 3+
71-76% = 4-
77-82% = 4
83-88% = 4+
89-94% = 5-
95-00% = 5
I need to build excel sheets and be able to convert images to text. I copy the sheet from a web site and all fields in the table come in as text except for one. That field can be 1 of two images. Right now i have to save the xl sheet as html and then Find/Replace the html image code with a + or - sign which is time consuming. Is there a way I can convert he image though excel. I need to be able to sort the sheet as well as pull the data into a separate tab.
View 4 Replies View RelatedI am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.
I need to convert these to numbers.
I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding £1000 but this does not seem to work.
Postage.xlsx‎
I have a formula which extracts a number from a text string.
The text string is in A2 - the formula which extracts the number is in B2
However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number.
Some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.
I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates.
View 7 Replies View RelatedHow can i convert text columns to numbers in VBA.
View 8 Replies View Relatedi am having a problem here,i Have got two columns one for item name other for item quantity.the quantity field has data like:
2+2+8
5+5
2+9
5
666+98
258
6+89+7+4+2+4+5
.....etc.
i need to calculate the total of all the cells like the first has sum 12,second has 10 ,fourth has 5 .Now i can get the sum by simply putting a " = "
in the start of the cell but the problem is that ive got 8000+ cells putting = manually will kill me.
On my Excel spreadsheet, I have a TEXT value in A1 that is all numbers, i.e. "000065200". I would like to write some VBA code that changes A1 to the numeric value . How do I do this?
View 9 Replies View Related