[USER="299865"]AliGW[/USER] Hey. Here is the the example of what I meant.
I tried using your method Data > New Query > From File > From Workbook and this popped out (attached as the .png file). It starts reading from A1, not the table on its own. I'll be waiting for your feedback.
Thank you in advance.
Are you changing the header after you have created the query? I think that this is the part we are not understanding clearly.
In the original file, I changed one of the headers to read RED. I then clicked on the Refresh button twice and the query was updated without issue. Again, I am unable to replicate your issue.
Yes, I change the header of the original table after I have created the query.
That's why the query turned into an error afterwards.
[USER="299865"]AliGW[/USER] Apologies. I didn't mean to sound rude or disrespectful. And I do appreciate your answer, so thank you.
Yes, your method does work. I tried it with a new workbook with new tables.
What I meant was your method somehow doesn't work on the workbook that I'm working on - which is weird.
When I get data from the file, the tables are listed as the sheet. So if I have 3 tables in that sheet, it's being read as 1 big table starting from cell A1 (my table starts from A5).
Example:
I have 5 sheets named after Client 1, Client 2, Client 3, and so on - where table Client 1 always start at cell A5.
Each sheet has 3 tables with these tables names: Client1_Pot, Client1_Vase, Client1_Mirror - where Client1_Vase is below Client1_Pot, and Client1_Mirror is below Client1_Vase.
When I tried applying your method, (Get Data > From File ...), I can't select my tables (Client1_Pot, Client1_Vase, Client1_Mirror, etc), because all the tables are being read as the sheet's name: Client 1, Client 2, Client 3, and so on. And I preview the "table", it shows: Client1_Pot, Client1_Vase, Client1_Mirror, merged into one big table starting from cell A1. And that's my issue.
I truly apologize if my explanation isn't clear enough. I can't attach the real file for it is a matter of confidential data nor can I attach a new file with it since my issue doesn't apply on new workbook.
So, if there's any other solution that you can suggest then I'll be truly grateful. If not, then thank you for taking the time to even respond in the first place.
Have a great day.
Why not just change the header in the original Excel SpreadSheet. I am not sure I am understanding your situation.
I can change the header in the original Excel Spreadsheet. But when I do that, the query will resulted in error like the example I gave you in the file.
Let's say I made the "Red" in the original Excel Sheet and I made the query from that table.
When I change "Red" to "Pink" in the original table, the query resulted in error because it's looking for the header called "Red" which I already changed to "Pink".
To solve that error, I need to go to advanced editor in the query and manually change the "Red" to "Pink" (picture attached in the excel file).
Is there a way to change the header in the Sheet without making an error in the query?
Hello. The problem is that I have 3 active tables in a sheet. Your method doesn't work. And I do not want to load it in a new workbook. It takes a long time to load everything using your method. Is there any other way?
The header does not change in the original table. It changes in the query and then when you Close and Load the query to Excel, it will be changed in the output table.
Well that is why I open this thread. I need to change the original table header's name, not the query.
Is there a way to do fix this?
If cannot, then is there a way to change all of the queries header's name? Not one query at a time. Selecting multiple queries and change the name header's name all at once.
Dear Excel Gurus,
This might sound so basic but as you know, you transform a table into query by clicking the "From Table" button in the Data ribbon.
I currently have 100+ tables in my workbook and it is inefficient to transform all those tables manually one-by-one.
So, is there a way to convert all of them automatically? And, if possible Load as Data Connection only?
Thank you in advance.
Have a great day 
Unable to replicate your issue. I was able to change several column names without issue.
Hello, there! Thank you for your time to reply my issue.
It seems like you changed the column names IN the query itself but not the one in the sheet.
e.g. You changed "Yellow" to "Orange". The header's name in the query is Orange but the one that you see on the sheet is still Yellow.
Just to clarify, is that what you did or is my version of excel and/or power query too outdated compared to yours?
Will be waiting for your feedback.
Have a great day!
Dear Excel Gurus,
Pardon one of your weaklings for asking your guidance on one of the quest to master Excel.
As you probably know, you need to make a table to be able to use the Power Query function.
I have lots of sheets with identical tables. Sheets are named based on my clients.
I am currently stuck with one of the Power Query "setting" - changing the header's name of a table.
Once I change it, the query goes error. I have to change it manually in the advanced editor menu.
And it is a pain in the butt to do that one-by-one when you have more than 10 tables in the workbook.
I have attached the example file for you.
Please be the knight in the shining armor for me and help me solve this issue.
Thank you.
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795240Have added some data in Column A ... for tests ... and aligned sheet numbering ... to be on the safe side ...
Cheers
:smile:
Finally.. I kept getting error regarding colLtr. Apparently I didn't see the module *sigh*
I cannot thank you enough really. You've helped me and I really appreciate your time, understanding, patience, and attention.
I can't emphasize this enough but THANK YOU 
Re: How to use indirect formula to show value AND formatting?
It's not empty at all.. If you scroll down up to row 90, lots of data.
So sorry to burden you with my incompetence :(((
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795233Hello Carissa,
Attached is a tiny modification ... :wink:
Hope this will help
Hey, it's not copying 
What should I do?
Re: How to use indirect formula to show value AND formatting?
Well this is the real file with some tweaking cause I cannot show you what it is haha.
I did change the range etc basen on my understanding of your solution. What did I do wrong?[ATTACH=CONFIG]72998[/ATTACH]
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795226Display MoreHello Carissa,
To answer your two questions:
1. The +4 is in fact +2 plus +2 ... the first +2 is due to the placement of your Table starting in row 3 ... and the second +2 is needed since within your source worksheets, all your columns A,B,C,D are in fact place 2 columns on the right ... A is in Column C, B is in Column D, etc ...
2. The Sheet3.Range("M" & x) is the equivalent of the Indirect function ... since the Column Letter which is to be used is located in Column M ... and your Source worksheets have their data always located in Row 2 to Row 6 ...
Hope these explanations are clear enough ...
Cheers
Carim
Oh and in your attached file, when I change the Company to E or F, there's a debug error
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795226
1. The +4 is in fact +2 plus +2 ... the first +2 is due to the placement of your Table starting in row 3 ...
Which table are you talking about? Row 3?
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795222Attached is your workbook with the explanations of each instruction ...:wink:
:smile:
Hey.. 2 questions:
x = Application.Match(Sheet3.Range("D5"), Sheet3.Range("M3:M8"), 0) + 4
Why is it +4?
Sheets(Sheet3.Range("K4")).Range(Sheet3.Range("M" & x) & "2" & ":" & Sheet3.Range("M" & x) & "6").Copy
Why it "M" and "2" and "6"?
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795217Attached is your sample workbook ... to be tested ... :wink:
Hope this will help
THANK YOU SO MUCH! U'VE SAVED MY LIFE!
Is it possible for you to tell me what each of those codes mean? I really want to learn VBA cause it comes in very handy.
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795213Thanks for your file ...:wink:
Do we agree, both the copy and the formatting should take place as soon as you select the Column # in cell D5 ...?
Editable data are "Sheet Name" in cell K4 and Company's name which is in Cell D5. The "Column #" value depends on Cell D5.
But yeah.. both the copy and formatting should take place as soon as I change the value on one of the cells, either K4 or D5.
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795211Display MoreBelow is an explanation ...
How to Attach a Sample Workbook
To attach a workbook:
1. Click 'Go Advanced' at the bottom right of the Reply Box.
2. In the new window click the Paper Clip Icon.
3. In the new window that pops up click 'Add Files'.
4. Click 'Select File' and browse to the required file.
5. Click 'Upload File'.
6. Click 'Done' when the selected file appears in the box at the bottom of the window.
The maximum file size allowed is 3MB, if your file is larger than this you can try zipping it to reduce the size. If still too large, or if your actual file contains sensitive data, then create a sample workbook with reduced amount of data or with sanitised data.
When creating a sample workbook it is essential that the structure of the actual workbook is retained.
• If data has been sanitised, then the sanitised data must be of the same type as the original (text still text, numbers still numbers, dates still dates etc.).
• The Layout of data on each sheet remains the same.
• If actual data contains blank rows, columns or cells then include the same in the sample data.
• If a cell contains a formula keep the formula in the sample, in other words do not create the sample file by pasting just values from the actual file.
Thanks! Did it already.. Can you tell me how to cancel editing too? lol.. couldn't find the button.. or even to delete a reply..
Re: How to use indirect formula to show value AND formatting?
Quote from Carim;795209To be on the safe side, just attach a sample workbook with your next message ...:wink:
I did.. https://ufile.io/l4env
sorry I don't know how to attach file.. hehe