I'm sure there is a simple fix to this so forgive my ignorance. I read through some older posts about auto complete but still can't seem to work out this simple problem....
On Sheet A, Cell B2, I would like to display any data entered into Sheet B, Cell B1. This is simple enough (='SheetB'!B1 in Sheet A, Cell B2), but I'm having problems using auto complete to drag the formula horizontally.
Sheet A's data is organized vertically by data types where as Sheet B's data is organized horizontally. Ideally, if I filled in Sheet A, Cell B2 with the formula above and then dragged it horizontally across, the cell mapping would look like this:
Sheet A, Cell B2 ---> Sheet B, Cell B1
Sheet A, Cell C2 ---> Sheet B, Cell B2
Sheet A, Cell D2 ---> Sheet B, Cell B3
Sheet A, Cell E2 ---> Sheet B, Cell B4
etc....
When I use the auto complete feature it instead does this:
Sheet A, Cell B2 ---> Sheet B, Cell B1
Sheet A, Cell B3 ---> Sheet B, Cell C1
Sheet A, Cell B4 ---> Sheet B, Cell D1
etc....
I've tried putting the '$' operator in front of the Column letter to prevent auto complete from increasing it when I drag horizontally (ie (='SheetB'!$B1) but all that does is stop auto complete from increasing it at all and it ends up looking like this:
Sheet A, Cell B2 ---> Sheet B, Cell B1
Sheet A, Cell C2 ---> Sheet B, Cell B1
Sheet A, Cell D2 ---> Sheet B, Cell B1
How do I fix this? I realize that data should ideally be stored vertically but I need to keep this current format (unfortunately). I've uploaded the excel file as well to better illustrate what I am trying to describe. Hope that wasn't too confusing and sorry if this is a rookie question. Thanks in advance
-DJ
You'll need to use a completely different formula utilizing INDEX or INDIRECT. So in B3 of your active sheet, try
=INDEX(Test!$B$1:$B$10,COLUMN(A1))
or
=INDIRECT("Test!B"&COLUMN(A1))
dragged across.
Does that work for you?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
I'm sure its error on my end but I can't get either of those formulas to work. The cell displays "FALSE" for both. Any suggestions?
I prefer lookups. Have you tried something like the following?
=HLOOKUP($B$2,'Data Interoperability Conf'!$B$1:$B$10,2)All you need to do is copy horizontally and it should work.
Unfortunately it still doesn't seem to work. The first cell displays the correct data but after copying horizontally, the formula does not change and the same data is displayed across all cells.
I'm sure it's user error so if you can post a excel doc with it working properly that would be beneficial and much apprieciated.
Change the last digit for each column.
=HLOOKUP($B$2,'Data Interoperability Conf'!$B$1:$B$10,2)
=HLOOKUP($B$2,'Data Interoperability Conf'!$B$1:$B$10,3)
=HLOOKUP($B$2,'Data Interoperability Conf'!$B$1:$B$10,4)
and so for
Here is the spreadsheet with my two example formulas used.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Perfect! thanks to all for the assistance. It is much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks