Auto fill

%3CLINGO-SUB%20id%3D%22lingo-sub-1685303%22%20slang%3D%22en-US%22%3EAuto%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685303%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20a%20complex%20table%20that%20contains%20something%20like%20750%20rows%20and%20200%20columns%3C%2FP%3E%3CP%3EA%20lot%20of%20the%20columns%20containing%20logic%20and%20calculations%20are%20hidden.%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20be%20able%20to%20do%20is%20copy%20a%20row%20so%20that%20only%20the%20visible%20data%20is%20copied%20and%20pasted%20into%20another%20sheet%20using%20a%20link.%20Everything%20I%20have%20tried%20results%20in%20everything%20being%20copied%20including%20the%20hidden%20stuff.%3C%2FP%3E%3CP%3EEither%20that%20or%20I%20would%20like%20to%20be%20able%20copy%20three%20sets%20of%20the%20visible%20data%2C%20paste%20a%20link%20then%20at%20the%20link%20auto%20fill%20the%20remaining%20columns%20base%20on%20the%20progression%20demonstrated%20in%20the%20first%20three%20columns.%20So%20in%20the%20following%20I%20want%20to%20auto%20fill%20rows%20based%20on%20the%20three%20completed%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A.%20%26nbsp%3B%20%26nbsp%3BColumn%20B.%20%26nbsp%3B%20%26nbsp%3BColumn%20C%3C%2FP%3E%3CP%3ESheet1A2%20%26nbsp%3B%20%26nbsp%3B%20Sheet1A12%20%26nbsp%3B%20%26nbsp%3BSheet1A18%26nbsp%3B%3C%2FP%3E%3CP%3ESheet1D2.%20%26nbsp%3B%20%26nbsp%3BSheet1B12.%20%26nbsp%3B%20Sheet1B18%3C%2FP%3E%3CP%3ESheet1G2.%20%26nbsp%3B%20%26nbsp%3BSheet1G12%20%26nbsp%3B%20%26nbsp%3BSheet1G18%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20it%20will%20do%20it%20because%20its%20a%20computer%2C%20I%20just%20don%E2%80%99t%20know%20how.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1685303%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1685421%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685421%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F799200%22%20target%3D%22_blank%22%3E%409191rogerm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EI%20know%20it%20will%20do%20it%20because%20its%20a%20computer%2C%20I%20just%20don%E2%80%99t%20know%20how.....%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20love%20that%20attitude.%20My%20own%20variation%20on%20it%2C%20for%20years%2C%20has%20been%20%3CEM%3E%3CSTRONG%3E%22If%20you%20think%20'They%20should%20have%20made%20it%20possible%20for%20Excel%2FWord%2Fwhatever%20to%20do%20______________%2C'%20then%20you%20can%20take%20it%20as%20a%20given%20that%2C%20in%20fact%2C%20%22they%22%20did.%20Your%20challenge%20is%20to%20find%20HOW%20they%20did%20it.%22%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20mean%20finding%20a%20menu%20item%20that%20isn't%20worded%20the%20way%20you%20would%20have%20worded%20it.%20In%20the%20case%20of%20Excel%2C%20it%20more%20often%20means%20finding%20a%20function%20with%20some%20weird%20name%20like%20VLOOKUP%20or%20INDEX%20or....%26nbsp%3B%20The%20other%20thing%20about%20Excel%20that%20both%20enchants%20and%20infuriates%20is%20that%20there%20probably%20are%20at%20least%20five%20different%20ways%20to%20accomplish%20any%20given%20task.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20case%2C%20for%20example%2C%20I%20can%20think%20of%3C%2FP%3E%3CUL%3E%3CLI%3E%3CSTRONG%3EVLOOKUP%3C%2FSTRONG%3E%3A%20once%20you've%20put%20column%20A%20in%20place%20on%20your%20secondary%20sheet%2C%20perhaps%20by%20copying%20it%20over%20(but%20there%20are%20other%20methods%2C%20to%20be%20mentioned%20below)%2C%20then%20use%20VLOOKUP%20to%20populate%20the%20rest%20of%20the%20columns.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EINDEX%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EMATCH%3C%2FSTRONG%3E%20to%20do%20much%20the%20same.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EUNIQUE%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%2C%20perhaps%20combined%20with%3CSTRONG%3E%20SORT%2C%20%3C%2FSTRONG%3Emuch%20newer%20functions%20available%20only%20the%20most%20most%20recent%20versions%20of%20Excel%2C%20that%20could%20be%20really%20good%20to%20meet%20your%20need%20if%20you%20have%20that%20recent%20version%3C%2FLI%3E%3CLI%3EThere's%20even%20%3CSTRONG%3EPivot%20Table%2C%3C%2FSTRONG%3E%20although%20based%20on%20your%20description%20I%20somehow%20think%20it%20would%20not%20be%20applicable.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20question%3A%20are%20you%20mainly%20wanting%20that%20second%20sheet--where%20the%20copy%20of%20only%20visible%20data%20is%20to%20reside--to%20be%20simply%20for%20the%20viewing%3F%20I.e.%2C%20is%20it%20solely%20for%20%3CU%3E%3CEM%3Eoutput%3C%2FEM%3E%3C%2FU%3E%2C%20or%20are%20you%20going%20to%20be%20expecting%20yourself%20or%20others%20to%20make%20entries%20of%20data%20in%20some%20cells%3F%20That%20would%20make%20a%20big%20difference%2C%20a%20very%20big%20difference%2C%20in%20how%20you%20should%20proceed.%20%3CU%3E%3CEM%3EMy%20hope%20is%20that%20all%20%22maintenance%22%20of%20information%20is%20to%20be%20done%20on%20the%20%22source%22%20sheet.%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20you%20to%20post%20a%20%3CU%3E%3CSTRONG%3Esample%3C%2FSTRONG%3E%3C%2FU%3E%20of%20what%20your%20sheets%20contain%2C%20just%20making%20absolutely%20sure%20first%20that%20no%20proprietary%20or%20private%20information%20is%20contained%20it%20it.%20To%20the%20extent%20there%20are%20names%20and%20addresses%2C%20either%20delete%20them%20or%20substitute%20names%20of%20movie%20characters%20and%20false%20addresses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi, I have a complex table that contains something like 750 rows and 200 columns

A lot of the columns containing logic and calculations are hidden.

What I want to be able to do is copy a row so that only the visible data is copied and pasted into another sheet using a link. Everything I have tried results in everything being copied including the hidden stuff.

Either that or I would like to be able copy three sets of the visible data, paste a link then at the link auto fill the remaining columns base on the progression demonstrated in the first three columns. So in the following I want to auto fill rows based on the three completed columns

 

Column A.    Column B.    Column C

Sheet1A2     Sheet1A12    Sheet1A18 

Sheet1D2.    Sheet1B12.   Sheet1B18

Sheet1G2.    Sheet1G12    Sheet1G18

 

I know it will do it because its a computer, I just don’t know how.....

 

 

1 Reply
Highlighted

@9191rogerm 

 

You wrote: I know it will do it because its a computer, I just don’t know how.....

 

I love that attitude. My own variation on it, for years, has been "If you think 'They should have made it possible for Excel/Word/whatever to do ______________,' then you can take it as a given that, in fact, "they" did. Your challenge is to find HOW they did it."

 

It might mean finding a menu item that isn't worded the way you would have worded it. In the case of Excel, it more often means finding a function with some weird name like VLOOKUP or INDEX or....  The other thing about Excel that both enchants and infuriates is that there probably are at least five different ways to accomplish any given task.

 

In your case, for example, I can think of

  • VLOOKUP: once you've put column A in place on your secondary sheet, perhaps by copying it over (but there are other methods, to be mentioned below), then use VLOOKUP to populate the rest of the columns.
  • INDEX and MATCH to do much the same.
  • UNIQUE and FILTER, perhaps combined with SORT, much newer functions available only the most most recent versions of Excel, that could be really good to meet your need if you have that recent version
  • There's even Pivot Table, although based on your description I somehow think it would not be applicable.

 

A question: are you mainly wanting that second sheet--where the copy of only visible data is to reside--to be simply for the viewing? I.e., is it solely for output, or are you going to be expecting yourself or others to make entries of data in some cells? That would make a big difference, a very big difference, in how you should proceed. My hope is that all "maintenance" of information is to be done on the "source" sheet.

 

Is it possible for you to post a sample of what your sheets contain, just making absolutely sure first that no proprietary or private information is contained it it. To the extent there are names and addresses, either delete them or substitute names of movie characters and false addresses.