Hello,
I am having trouble using the OFFSET function. I have several columns with varying row lengths that I am trying to combine into one column. I was trying to program so that it would go through all the rows in one column until it reached a blank cell (using the =if(ISBLANK()=True,) then it would offset to the top of the next column and begin copying those rows.
I have attached an example and appreciate any help to solve this issue:
OFFSet.xlsx
This will work for the example povided
in A4 array entered and copy down
=IFERROR(CHAR(SMALL(IF($D$5:$H$8<>"",CODE($D$5:$H$8)),ROWS($A$4:A4))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hello thank you for your help but there seems to be an issue. You are referencing the cell A4 in your code. This is the cell that I want to paste the formula which will pull the data from the table. There will be no list to check, I only provided the list in column A to show what I hope the results will look like after the formula has been placed.
The formula does not reference A4 cell, the formula starts in A4. You wanted to combine several columns and that is what formula did.
Please see attached file
For some reason though when I change what is in the table, for example an "a" to an "m", it does not reflect into the column.
Sorry, but I have no idea what you going to change it to next. I wrote this formula based on what you provided.
Is there a way to have the formula update column A is there are changes in the table?
This is what caused my initial confusion with my first reply.
Would it be ok if the order changed to this?
a
b
c
d
e
a
b
c
d
e
b
c
d
c
d
Yes. It can change to any order as long as it would update if a cell was changed in the table. Thanks
In A4 array entered and copied down
=IFERROR(INDIRECT(TEXT(SMALL(IF($D$5:$H$8<>"",ROW($D$5:$H$8)*10^4+COLUMN($D$5:$H$8)),ROWS(B$4:B4)),"R0000C0000"),0),"")
with this formula you can change letters to words if you have to.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Great the function now updates from the table.
The only problem is it is now pulling the info from the table from left to right instead of top to bottom. Is there a way to have it pull top to bottom for each column?
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks