Search results

Thanks Debaser. Was hoping I was missing something since other formula actions do work there, but does not seem to be the case here. Thanks for the feedback

Thanks for the feedback, p45cal. Agreed, this can be managed that way. Was just trying to push the envelope and avoid that intermediary step, but I acknowledge this will work

Thank you for your feedback, Michael, much appreciated. I was looking for a solution where I did not need to generate the sorted and unique list (C42), and then reference that. I am trying to build this directly from the structured reference table, something like the...

Hello I am working with structured references (tables). I have a table, (example enclosed), that contains data. I have kept the example simple. The table is a data table with one column called Number. The table name is Table1. For the application I am trying to address, this example table...

Hello Somendra A deceptively simple solution that works well.o_O Thank you for your help. Sergio

Hello I have attached an example file. I am trying to code a user defined function, (UDF), where I am looking up, and concatenating a first name and last name, based on initials, in a table. My issue is that the table will not always have values, which is logically fine. Unfortunately when...

Thanks Mike, that works well, as well. This leaves me with a few options although each option still obliges multiple functions. Perhaps one day Microsoft with bring an enhancement where a formula like VLOOKUP would be smart enough to recognize the table column name and use the its column...

Hi Perhaps a little confusion here. I do want to look up $D$12 in the first column of the table and I want to retrieve the related value from the column [Holiday]. While this column is the second column in the table, if I enter the number 2, and then later insert a column, it breaks the...

Hi I would like to use a VLOOKUP referring to a table as follows; =VLOOKUP($D$12,TABLE1,TABLE1[HOLIDAY],FALSE) instead of =VLOOKUP($D$12,TABLE1,2,FALSE) I am not clear if this is doable without additional formulas, or with which formulas to use if more are needed. Thanks Sergio

Hi Narayan Thank you for the quick reply. I greatly appreciate it. Sergio

Hi I am trying to summarize costs from a transactions table into a report by the group I have assigned to payees in a reference table. To get the correct value I would read the transactions table, take the payee, look it up in the reference table that shows the payee and the group it is...

Thank you Narayan I thought that INDIRECT() was what I needed to use but I had the syntax wrong. Actually what I did with your help is the following: =SUM(INDIRECT(VLOOKUP(J8,t_TableNames,2,FALSE)&"[[#Totals],[Amt]]")) This will allow me to select a unique value and then have related data...

Hi I am trying to determine how I can use the name of a table in a formula where I determine the name of a table depending on a lookup value. This is what I mean: I have two tables in a worksheet; table t_One and table t_Two. I want the total for a column in the table but I want to determine...

Both methods; Ctrl+Shift+Enter to create an array formula of the original attempt, and using SUMPRODUCT instead of SUM, worked. Thank you for your help. Sergio

Hi I have created a small example file of the data above. The formula is there once as it works and once it does not, for each of two lists Thanks Sergio

Hi I have a table, (tbl1), with columns "trans" and "amt" (simple example). I have a separate table, (tbl2), of possible transactions that can be used in the "trans" column of (tbl1). (tbl2) has only 1 column. From (tbl2), I created a defined name, (lst1), referring to the single column of...

On the presumption that you have a left arm, then perhaps... otherwise it is a way to stretch back muscles ;-) This is an attempt at creating an investment transaction file to report from. The table of securities is to have a validated list to select the security for which I want to record a...

Hi SirJB7 I did as you suggested, (and I did not cheat!) so if I executed correctly, the link should take you to my sample file. Thanks Sergio https://www.dropbox.com/s/65l34c2p8hsoqua/Chandoo_Validation.xlsx?v=0mcnc

Hi everyone Someone wise suggested I introduce myself. My name is Sergio and I am in Toronto Canada. While I do not work with Excel as much, I am a bit of an enthusiast for personal needs. Thanks for the help I have received so far and I look forward to using this site to learn even more...

Hi SirJB7 How do I upload a file? Thanks

Hi SirJB7 I first created a table, then created a named range (Defined names > Name Manager) that refers to the table column. Then, using the data validation icon, I choose list and enter the name I just created. So for example, the table name is << tblStock >>, and the...

Hi Here is what I am trying to do. I have a reference table. The table has three columns; "Symbol", "Security" and "Description". The value in "Description" is "Symbol" and "Security" combined. Here is an example: Symbol Security Description AAPL Apple AAPL-Apple BBRY...

Hi Okay, first of all, thank you! With your suggestions, I was able to achieve what I was after. My formula ended up looking like this: =VLOOKUP($E$1,tbl_INFO,MATCH(tbl_INFO[[#Headers],[Office]],tbl_INFO[#Headers],0),FALSE) This is what I was after. The benefits here are many, namely...

Thanks guys, but let me clarify what I am hoping to do. I created a table with the new (from Excel 2007) tables function (which replaces lists). The table name of the table I created was, (for example), << tblLocation >> and it has 3 columns; Employee, Office, Floor. If I...

Hi Luke Thanks, that was embarrassingly simple.;-) It works and I have a follow up question. Is there a way to use the table column name as a reference instead of the 2 or 3? I ask because it would potentially be a way to have some clarity in a larger, complicated spreadsheet.