I am aware of that function also but the combo box I would then have to scroll down and search for the word in my list.
Where as typing and getting the list of all combination of the letter would be much faster and easier.
As said in my first post "=s" would display all the combination of "=s"
One more request there should be only One Macro for substitute for Data validation should be applicable for entire column in "D" in Sheet2 and not a Macro for each Cell individually.
I have attached the sample file.
As the attached file is a sample the contents are completely different.
I have made 2 Sheets,
Sheet1 and Sheet2
In Sheet2 I have written brief note.
I have a List of Items in Sheet1 say around 3000 items, in column "B"
In Sheet2 in entire column "D" I want to create a Macro in such a way that when I type any Letter Eg. "A" it should display all items starting with Letter "A", after finding all options from Sheet1 from column "B"
Then after "A" if I type "AB" it should show all items beginning with word "AB"
Then "ABC" should show "ABC"
This is because if there is a spelling mistake it will sound alert.
For Example The system should work the same way when we type a formula "=s" it shows all the combinations for "=s"
I know this will work with data validation, But I want to create a Macro.
Because creating Data Validation and copying in entire column D will make the file large.
Thanks PecoFlyer and royUK, the formula is working fine, even after closing the excel file
Thanks a lot
Further I have also mentioned the range name in Name Manager
Sorry to bother you once again. Although the formula is well applied without any error,
But when I close the excel file completely and reopen the file the named table again changes to Range value
For Eg. the formula =IFERROR(VLOOKUP(B4,Table4[[Table B]:[Table D]],3,FALSE),"") after save the file and close the excel file completely and again reopen the file the formula again changes back to =IFERROR(VLOOKUP(B3,Sheet2!$B$2:$D$21,3,FALSE),"")
Attaching the file for reference
I could Do it, Thanks Very Much for Help.
I was actually applying the formula without formatting the Data.
ok, will try
I have created a table on Sheet1
Reference to Lookup is in Sheet2, wherein the entire table range has been named Table
Now in Sheet1 I want to create a formula based on range name but there seems to be an error in the formula which I cannot figure it out.
The Formula I want to create is in Sheet1 Row C
The formula should lookup the value in Row B, and put the correct figure from Sheet2, row D
What I want is shown from C6 onwards. I want to create a formula based on Range named as Table
The formula I have used us shown in Sheet1, Cell C3 that has a error
Attached file for reference
I have list of Expenses in Sheet2
Sheet1 contains the detailed expenses of each item
In Sheet3 in column B, When I select the list of expenses ( Already Done )
Then ( What I Want to Do is )
Column C should show only the expenses related to items selected in Column B, The list of Expenses is shown in Sheet1
I have a question on Excel Power Query, could you please let me know if this is the correct forum for help
I have list of numbers in column B
I want to create VBA such that it selects all numbers that the total would be equal to or nearest to number specified in cell F2
All selected numbers should be written in column D adjacent to selected one from column B
Then it should total the numbers and written in cell D19
Works perfectly fine, Thank You.
Yes the word Summary can be found ONLY in column A from A10 onwards till down, and anywhere.
I have attached a sample sheet as to what exactly it should look after VBA is applied.
Row A6 to AH6 is the data that needs to be copied, this data is already written.
In column A I have shown example of only 5 word where Summary are given but there can be many more or less than 5 in column A to down
Above the word Summary the entire Row A6 to AH6 is copied after VBA is applied.
I have data in columns A6:AH6
There are many other details in the entire sheet, lets say a word "Summery" comes many times from column A10 onwards
Whereever there is the word "Summary" from column A10 onwards I want VBA to copy entire text from A6:AH6 and paste it above the word Summery
I already have already created an empty row above the word Summary so no text would be replaced.
Great thanks, works superb
I have used your formula and it is giving error.
Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated.
I am attaching the excel sheet where I have applied the formula.
I am very much sorry for incomplete posting.
I have revised my attachment.
In sheet Apr, May and Jun there are more than 1 for places named 1, 2 and 5
What I require is
In sheet1 Cell B1, it should total all the cells from sheets Apr to Jun that matches Cell A8 in sheet1 and also matches cell B4 in sheet1
In my final sheet there are Apr to Mar (12 months) sheets.
Once again I apologize for incomplete post.