Posts by carissa

    [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?

    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.

    Files

    Re: How to use indirect formula to show value AND formatting?


    Quote from Carim;795240

    Have 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 <3

    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]

    Files

    • 01.xlsm

      (247.34 kB, downloaded 90 times, last: )

    Re: How to use indirect formula to show value AND formatting?



    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;795222

    Attached is your workbook with the explanations of each instruction ...:wink:


    :smile:


    Hey.. 2 questions:

    Code
    1. x = Application.Match(Sheet3.Range("D5"), Sheet3.Range("M3:M8"), 0) + 4


    Why is it +4?


    Code
    1. 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;795217

    Attached is your sample workbook ... to be tested ... :wink:


    Hope this will help


    THANK YOU SO MUCH! U'VE SAVED MY LIFE! <3
    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;795213

    Thanks 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?



    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..