table array

  1. S

    Trying to get Max value from column in Table and get 0.. (VBA)

    Sub Macro1() ' Sheets("issue raw data").Select Range("Table1[[#Headers],[Amount of matches]]").Select highest = WorksheetFunction.Max(Selection) answer = MsgBox(highest) End Sub So I have numbers like 1,2,3,4,5 in column, but for some reason it results in 0. I tried to add Dim...
  2. A

    Resultant value from a Multiple Table

    The File contains 6 tables. First 5 Tables named Apple, Orange, Mango, Grapes & Banana and it contains the data of it varieties and its respective cost. In 6th “Fruit” table, two columns are there. One is ‘Fruit name’ and the second was ‘variety counts’. If I choose any one fruit in a first...
  3. S

    [SOLUTION HELP]: Copy data from cell and pivot table into new table format.

    Hello, I need your help on EXCEL VBA solutions as per below requirements 1. Copy pivot table data in cell A9:A11 and C9:C11 in to sheet OUTPUT per example in sheet output. 2. Copy data from sheet INPUT cell G32,G36,G37,K32 (cut only first 2 digits),K33,K34,K35 (cut only first 3...
  4. B

    Variable VLookup based on Cell Value

    Hello Everyone Can anyone help me with my formula I need to do a VLookup on a Cell but the Table Array needs to be different based on the Value of another Cell. =IF(B5="Office",VLOOKUP($B$9,matrix!$D$2:$F$100,2,FALSE),IF(B5="Production",VLOOKUP($B$9,matrix!$AG$2:$AJ$100,2,FALSE),"0")) Any...
  5. D

    Curious problem with a find and replace macro?

    Hi Folks, I have a column of data (column H) headed [customer reference]. Customer reference data is populated by users when they book a courier online. They have to input a client customer number (3 digits), a sales order number and a check digit, each element separated by a hashtag (so...
  6. S

    Better way to input formulas in tables?

    So I have 8 different tables, some with up to 65 columns, others less. Current I have formulas in the columns for those which need formulas. I kind of want to transition away from having the formulas in-cell, where they can be changed, erased, etc., to having them be coded. However, I am...
  7. H

    Extract Rows/Cells from Master Table to Sorted Tables based on Suffix of Text.

    Hello fellow excellors, I have a number of assets that contain very specific suffixes, for example: XRD05_CV XRD06_CV XRD07_CV XRD08_CV_I XRD08_HB XRD08_WS XRD09_CV These assets are listed in a master table. as you can see, there are different suffixes such as _CV, _CV_I, _HB and _WS. Now I...
  8. M

    Updating Multiple Tables and Adding New Tables using a Reference Sheet from Master Workbook

    Workbook A (WB_A)is used by multiple people saved to their local computer. Saved in the same folder is another workbook (WB_B) that is used as a reference. WB_A pulls information from WB_B and populates multiple tables with rows. I need to be able to add new Tables to WB_B so that WB_A can pull...
  9. F

    Does value exist in 2 or more table arrays (VLOOKUP)

    I want find out if B2 in Sheet1 exists in column B or C in Sheet2. If B2 exists in either column B or C in Sheet2, I would like to display Yes or True. If B2 does not exist column B or C in Sheet2, I would like to display No or False. For example: Sheet1 <tbody> A B C (Does value exist in...
  10. L

    VBA not finding correct value.

    Hello, i am working on a VBA that should find a match in the table column A of sheet 1 and match it in a table on sheet 2 and select column 6 cell value. but when i run the code it selects the wrong row and value. what i'm i doing wrong. Sub test() Dim ws1 As Worksheet Dim ws2 As...
  11. G

    VLOOKUP Table Array (different Workbook) Based on Another Cell's Value

    Hi All, I'm trying to VLOOKUP a value in different Workbook (could be Open or Close). However the Table Array information, I would like to get determined by the value of another cell so that in case if I have to change the lookup worksheet & its path in multiple formulas, I would have to change...
  12. B

    Using OFFSET and MATCH to return the correct value

    I have a problem with using the MATCH formula to return the correct 'ranking' from my named array. I have the following: Cell Q3 - with options VL,L,M,H,VH Cell R3 - with options VL,L,MLH,VH Cell T3 should return an overall rating depending on what the user has selected from Cell Q3 and R3...
  13. T

    Constructing new table based on elements of a pre-existing table

    Hi, I am having trouble working out how to construct a new table based on the elements of an existing table. I'm pretty sure it's a combination of VLOOKUP and MATCH but he could also use INDEX. Here is the master table: <style type="text/css"> table.tableizer-table { font-size: 12px...
  14. S

    VLOOKUP formula inside Table_array

    Hello, I am trying to have vlookup search another sheet for a certain value. The problem is I want it to search the sheets in the table_array based on one of the other cells. I want it to look at what value I have in b9, so the formula is =VLOOKUP(DATE(P5,1,1),B9!$A$3:$F$2520,2,TRUE) but when I...
  15. E

    Automatically rename all tables on a worksheet

    Hi all! I am trying to rename all the tables on a worksheet and understand that I need to use VBA to do so.. I'm not very experienced so I was wondering if I can get some help here. Basically the layout of the table is like this: <tbody> Name Part 1 Name Part 2 Column1 Column2 Column3...
  16. S

    VBA: Multiply cells by weights

    I have a large table in the form: <tbody> Months August September October November December August 112 A B C D September 118 E F G October 231 H I November 142 J December 253 </tbody> Which only has diagonal values corresponding to the months...
  17. J

    Vlookup not working - table array turning red with $$

    I could use some help! When I try to do a v-look up on a spreadsheet I've used for 6 months the table array doesn't work. On the spreadsheet I'm pulling from when I go to highlight the columns it turns red and puts $ signs. Then it crashes my spreadsheets. I can't post a screenshot here but...
  18. G

    VLOOKUP table_array wildcard

    Using VLOOKUP, I'm comparing 2 sheets to check if the host is monitored. I extracted the list of guests in sheet1 and the list of hosts in sheet2 Sheet1 A1 Win-2K8-dev1 (335d5188-2e63-44cc-978a-51ea4c90e24c) Win-2K8-mail1 (345d5188-2w64-47cf-973n-53ef459he2rx) .. . Sheet2 A1 Win-2K8-dev1...
  19. J

    Add an extra row to a variant

    Hello, I have created the below code too populate a userform list box. However I am having problems adding a extra row. Function Populate_Listbox_Worksheet() As Variant Dim vaData As Variant nrow = Sheet9.Range("A1048576").End(xlUp).Row If nrow < Range("ProjName").Row + 1 Then vaData =...
  20. B

    Linear Interpolation of Data

    I am trying to write a formula which will linearly interpolate random sets of data in a table. For example, when used on the following table: <tbody> 0 1 2 3 4 5 15 20 0 10 0 15 20 5 20 </tbody> The output (in a separate table) should be: <tbody> 0 1 2 3 4 5 10 15 20...

Some videos you may like

This Week's Hot Topics

Top