Hi,
I'm no pro at Excel so I could use some help.
I have a sheet with two long columns of inputs. I'm changing and filling up these columns/cells regularly. Let's call this my working sheet.
In addition to this I'd like to have two other sheets that is mirroring the working sheet but sort it up in 4 and 6 columns. See pic.
Is this possible to do for a non pro like me?
/Bosse
sheet.png
Hi, and welcome to the forum.
I'm guessing this is a very simple example of your real world data. We tend to find with questions like this that the actual data is not quite like the example given so that any answer we give will not work. Neither is it clear what you mean by cell1, cell2, cell 3..etc.
Do these refer to specific contiguous cells like A1, A2, A3 or might they be say A15, A30, A45 ...?
Please upload your workbook with a sample of real data.
Finally what's the ultimate aim? Is it just the two grids you show in that layout and do you use these layouts in some further process?
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Richard's right... but I was having a go at htis, while he was posting. here's my effort, starting in N1 and AB1. I have left in the intermediate steps to make life easier if this isn't quite what you want. If it is, they can all be deleted.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi and thanks!
I sell used watches on hobby bases. I sell them in Facebook Buy&Sell groups.
In the sheet I have the groups. Continuously coming new groups (col 1) and continuously changing numbers of members in the groups (col 2).
I export the sheet as htm (as you can see, the groups are links), and work the groups from most members and down, using the 40 top groups in members.
I usually only have one watch to sell, but sometimes I have two or three.
So, to get an even reach if I have more than one watch to sell, I want these extra sheets.
I don't know how to ad a file here, so here's a link to the sheet:
http://www.jarnhall.com/my-sheet.xlsx
(English isn't my first language, but I hope you get what I want)
/Bosse
Here are my formulae, transposed onto your sheet.
Hi Glenn,
Thanks!
But since the links doesn't follow, it's of no use. It takes longer time to ad the links again than to copy and sort the columns manually.
Since you don't add the expected result, here my solution (as I read the question).
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi oeldere,
That doesn't make any sense to me. Does it show up as it should here? (see pic)
And I need the links to follow, otherwise it's of no use.
I'm on Mac OSX 10.10.3 and Excel 14.5.2
dump.png
I can't read the PNG, added on the forum.
Please also add manualy the expected result in your file.
1. It would help to see the workbook.
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Ah! The .png was missing...
Here we go again...
dump2.png
See my request in #9
Hm, I did. And the pic shows fine here. Here's another way to see it:
http://www.jarnhall.com/dump2.png
...and #2 where I cautioned about all this uncertainty. Don't upload pictures they are rarely much use.Originally Posted by oeldere
I'm back. Any solutions..?
Last edited by BosseJ; 07-04-2015 at 05:45 AM.
You are correct, my solution didn't copy over hyperlinks; but you did not explicitly ask for that to happen in your posts. It can be done, using a UDF to extract the URL in a macro enabled-file. Try this out.
Nops, didn't work. Shall I put up a png of how it looks at my end?
Please do.
Here it is
dump3.png
What happens when you click on any of cells in F or H?
What do you see when you hover over any of the cells in F or H?
Sorry about that! Since they weren't blue it didn't occur to me that I could click them...
So, yes, the links works and yes, the links shows when hover...
This is great! Can you do this with the third option with six columns as well?
Yes. Here it is.
Is it OK now?
Man! This is amazing! When I grow I wanna be like you...
(a bit late since I'm retired, but non the less)
Thank you!!!
Have a great summer!
/Bosse
see the Attached file I am not sure about your requirement.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
LoL... you're welcome. As for summer... it's raining here today.
Hi Siva,
Your solution is beautiful. But still, I need the links to work in the extra columns as well.
see the attached file
Works like a charm! Absolutely great!
Now I have to be like you too when I grow up...
Thank you!!
(OT: can mention that the watches I sell, I buy from India!
Sorry guys,
I changed it back to unsolved.
There is a problem. As I said in post #4, the columns with (linked)groups and number of members will constantly change.
For both of your solutions there is a problem with that. I get the impression that your sheets are static. Or, I just don't know how to handle them.
If I delete one group/member (i.e. two cells), the formulas change to count/check one less row of cells. But if I add group/members the formulas doesn't "gear up" to notice this.
So, in the original file I gave you, I had 168 entries. If I delete 68 entries, your formula (or what it is...
Any solutions?
/Bosse
Im away from the PC. Did you try to copy the formula across and down again,starting from the top left?
in the formulas you see: $B$2:$B$168
If you delete the last (68) rows, the formula change in $B$2:$B$100
Dont delete the rows but clear the data and/or change the array in a higher number.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Hi Glenn,
I did now...
Or, to be sure I understood you right: I deleted your formulas exept the first row. Then I marked that row and draged and marked the full area and filled down. But nothing...
If this wasn't correct, I don't understand what I should copy. Or, more correctly, where I should paste it (since your columns are different in hight).
/Bosse
Hi popipio,
Ah! That's a good idea! If I change it to $B$2:$B$1000 and never delete, just clear, and just work with the cells within this 1000 I should be ok!
So, is this how I do?:
I delete (clear?) all the cells with formulas except the first row. Edit the formula in the first row's cells, mark all the cells down to 1000 and use Edit->Fill->Down?
(I'm not very experienced at this as you can understand.)
/Bosse
Yes, that I can see. But how? Should I edit every single cell? Or is the forumla editable somewhere else?
I tried to edit the first row, "fill down" to row 1000. Didn't work. It got #REF! in the new cells when I add a new group/members.
Last edited by BosseJ; 07-05-2015 at 01:51 PM.
Firstly, which solution are you using - mine or Siva's? Secondly, please post your sheet (NOT a png)
The most easiest way is to use 'find and replace'(ctrl H)
find: $B$2:$B$168
replace: $B$2:$B$1000
There is also a range $D$2:$D$168 and $C$2:$C$168
Change that too
Last edited by popipipo; 07-05-2015 at 02:00 PM.
I guess you must be using Siva's solution, as mine will work perfectly without changing ranges - there aren't any to change. So, I'll bow out at this point.
Hi Glenn,
Nops, I'm not using any since non of them accept new entries (or, they accept, but doesn't count them in).
How do you do in your file when you add new entries to get it to work?
So post your sheet.
Oki...
I've been adding groups in a new sheet (since I couldn't get any of yours to work). So I've got nothing new to get you there.
But here is the file I'm working on:
http://www.jarnhall.com/my-facebook-groups.xlsx
(still don't know how to upload an excel book here in the forum)
see the attached file
see the attached file
Thanks!!
/Bosse
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks