#1
|
||||
|
||||
Fix the numerical table column headings to General or Number Format
Hi. In the attached workbook, the first sheet has a formula that pulls values from the second sheet according to the set criteria through the INDEX MATCH functions. My problem is that when I converted the source data into a table, the formula returned error results. The column headings which are numbers were turned into text. I tried formatting the headings into numbers and general but still the errors remain. Typing an apostrophe before the number in the first sheet just so the errors would be eliminated is incorrect.
The temporary workaround is to exclude the column headings in the table but the data source looks ugly. Any idea on how to straighten the column headings? Thank you. |
#2
|
|||
|
|||
=INDEX(Table2_Salary,MATCH([@[SALARY GRADE]],Table2_SG),MATCH([@[SALARY STEP]],Table2_Step+0))
Just add zero to the heading to make it a number Dont know why it did it though |
#3
|
||||
|
||||
Hi
|
#4
|
||||
|
||||
Thank you!
Both formulae work perfectly in my actual data where the INDEX MATCH are nested in an IF formula. Your advice Pecoflyer is well taken and noted. |
#5
|
||||
|
||||
You're welcome and thanks for the rep
|
#6
|
|||
|
|||
Headers for defined Tables can't be numbers (or formulas).
And for your case, your solution is too complex! |
#7
|
||||
|
||||
Thank you Arvil.
I didn't know that there is a much simpler formula to get the salary from a given matrix. i will post in a new thread a related problem. |
#8
|
||||
|
||||
[And for your case, your solution is too complex![/QUOTE]
Arvil may I ask about the function of the extra comma after tAnnex? |
#9
|
|||
|
|||
Quote:
tAnnex in formula returns datarange of Table tAnnex, i.e. a range A2 : D5 in example file. NB! This range is 2-dimensional, i.e. in general it contains rows and columns. For 2-dimensional ranges/array: INDEX(DataRange,RowNo,ColumnNo), returns a value from single cell; INDEX(RowNo,), returns a single row from datarange; INDEX(,RowNo), returns a single column from datarange; As follows, 'INDEX(tAnnex,,[@[SALARY STEP]]+1)' returns a datarange column ([@[SALARY STEP]]+1 from tAnnex, i.e. when SALARY STEP in tSalaries is 1, the column Step1 of tAnnex is returned, when SALARY STEP is 2, the column Step2 is returned, etc. |
#10
|
||||
|
||||
But why are there two commas in the formula?
=INDEX(tAnnex,, I tried deleting one comma but got error result. |
#11
|
|||
|
|||
Code:
=INDEX(Range,,ColumnNo) When you delete one of commas, you change the INDEX() formula to one with 1-dimensional range as 1st parameter. |
#12
|
||||
|
||||
Got it but quite. Thank you for the detailed explanation Arvil.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA convert to table - format Column 1 | Shelley Lou | Word VBA | 4 | 01-29-2021 05:44 AM |
Use a numerical merge field to subtract that number of months | MichaelSpedding | Mail Merge | 8 | 11-11-2015 01:11 PM |
VBA to Format a Table Column in Excel | Sgt Rock | Excel | 0 | 02-14-2015 02:54 PM |
1.image in a table 2.right click 3.menu click format 4.a format column appears | OldFatDog | Drawing and Graphics | 1 | 06-13-2014 11:19 PM |
Change from General Format to number format | gbaker | Excel | 3 | 08-16-2013 01:04 PM |